Trim off trailing letters
-
Hi all,
I need to trim off whichever letters are at the end of a reference number.
Example: If the reference number is ABC/12345A or ABC/12345ABBBBC or ABC/12345WRBV , it should be left with only ABC/12345.
Thank you in advance! - alan.leow - Tuesday, September 26, 2017 10:17 PM
Will the reference part that you need always be AAA/11111? For example, could the reference AA/11AAAA exist, and you only require AA/11? If not, then using the LEFT function will work:
WITH Reference AS
(SELECT *
FROM (VALUES('ABC/12345A'),('ABC/12345ABBBBC'),('ABC/12345WRBV')) R(N))
SELECT LEFT(N,9) AS Ref
FROM Reference;Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk - Thom A - Wednesday, September 27, 2017 1:48 AM
Oh... i forgot to explain that the reference number varies, yes it is not fixed at AA/11. It can be
BB/22AAAA or
CED/ABD11/124565 or
CED/ABD11/124565ABC.That's why i need to see if there are any alphabets at the end of this field, i have to remove them.
Thanks so much!
- alan.leow - Wednesday, September 27, 2017 1:53 AM
Could you provide several different scenarios and what the results you are expecting are? This help to ensure that we're all on the same page. Also, provide them in a format we can consume (see the example I gave above), as it makes it easier for us to manipulate your data.
Thanks.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk - Thom A - Wednesday, September 27, 2017 2:06 AM
Hi Thom,
Thanks for your help. I'm not quite sure what do you mean when you request for a format that you can accept. If I were to quote your example,
AA/11AAAA should return AA/11
AA/12345 should stay as AA/12345
AA/12345AB should return AA/12345
AA/METAL2345ABC should return AA/METAL2345
BAC/WATER456A/123CC should return BAC/WATER456A/123That's all. Basically, any letters at the end of the last set of numbers found should be removed.
-
"In a format we can consume" means we can just run the code and don't have to fiddle about copying and pasting your data on to our screen. It's you that's asking us for help, after all.
Never mind - try this:
WITH Reference AS (
SELECT N
FROM (VALUES
('AA/11AAAA')
, ('AA/12345')
, ('AA/12345AB')
, ('AA/METAL2345ABC')
, ('BAC/WATER456A/123CC')
) R(N))
SELECT LEFT(N,LEN(N)-PATINDEX('%[0-9]%',REVERSE(N))+1)
FROM Reference;String manipulations aren't T-SQL's strong point, incidentally. If it's an option, consider doing this in your presentation layer instead
John
- John Mitchell-245523 - Wednesday, September 27, 2017 2:28 AM
Hi John / Thom,
I think there's no need to copy any data. I have a field called REF_NUMBER which I have to manipulate and remove the trailing letters.
So effectively, there should be 2 columns. One is REF_NUMBER, the other REF_NUMBER_TRIMMED.So I just run this query in my existing table and it will display each reference number, with the other column showing a trimmed value.
Roughly, my query should be something like this:
SELECT
REF_NUMBER,
(The code to trim the reference number) as 'REF_NUMBER_TRIMMED'
FROM TABLE_NAME
Sorry if I might have sound misleading in my previous posts... - alan.leow - Wednesday, September 27, 2017 2:38 AM
John's point was that the data you provided above isn't in a format we can simply "use". We would need to copy your text, trim parts off, and then convert it into a format that SQL Server can use. If you tried to simply work with the words you pasted, SQL Server would simply throw as error, as it has no idea what the following means:
AA/11AAAA should return AA/11
AA/12345 should stay as AA/12345
AA/12345AB should return AA/12345
AA/METAL2345ABC should return AA/METAL2345
BAC/WATER456A/123CC should return BAC/WATER456A/123Instead, John converted your English statements into a SQL Statement, that contains the values we required in a CTE, so that we can interact with your data. John has given you the answer on how to apply the logic you require to that CTE, so you need to take the part of the statement that does the work (the part with the PATINDEX) and put it in your own SQL Statement.
Don't forget, we don't have access to your data, your SQL Server, etc. We can't see what your objects are, what your data looks like, hence the need to supply data be a consumable format for other users. For a better details and/or explanation on how to post T-SQL questions, have a look at the link in my signature.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk - John Mitchell-245523 - Wednesday, September 27, 2017 2:28 AM
I've found that the result of doing things like this is rarely needed in the presentation layer.
If this information is needed for search or grouping criteria in the database, I'd be seriously leaning make a persisted computed column that could be indexed for this problem.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Jeff Moden - Wednesday, September 27, 2017 9:29 AM
Absolutely. It may be needed in the presentation layer, or for other stuff in the database. That's why I was careful to use the word "consider".
John
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply