Concatenate & Trim String

Question

Can anyone help me, I have a problem regarding on how can I get the below result of data. refer to below sample data. So the logic for this is first I want delete the letters before the number and if i get that same thing goes on , I will delete the numbers before the letter so I can get my desired result.

Table:

SALV3000640PIX32BLU
SALV3334470A9CARBONGRY
TP3000620PIXL128BLK

Desired Output:

PIX32BLU
A9CARBONGRY
PIXL128BLK

What problem was that? What do you actually want to do? Chop off everything before the first letter after a number? Sample data is all well and good, but it doesn't specifically explain what you want, nor does it necessarily reflect all the real data you might put into the queries people suggest. You should explain in as much depth as possible so that people can provide relevant answers. This is too unclear. — underscore_d, Jul 26 '17 at 09:38
Your right, I actually want to chop this to get the desired result. I want to delete first the letters before the number and if I get that then I want to delete now the numbers before the letter so I can get my desired result. but I don't know ho or I don't have idea. — Jacky Montevirgen, Jul 26 '17 at 09:43
You should edit your question to include that info and anything else that could help readers understand and answer it adequately. — underscore_d, Jul 26 '17 at 09:45
@underscore_d so you understand now on what iam supposed to do? — Jacky Montevirgen, Jul 26 '17 at 09:48
What if your string starts with a number? or it's all numbers or all letters? What if your string is `asdf5345`? should that return an empty string? — Zohar Peled, Jul 26 '17 at 09:51
@ZoharPeled it will not start with a number. all of my data starts with letters. — Jacky Montevirgen, Jul 26 '17 at 09:54

score 4 · Accepted Answer · answered Jul 26 '17 at 09:54

4

You need to use a combination of the SUBSTRING and PATINDEX Functions

SELECT
SUBSTRING(SUBSTRING(fielda,PATINDEX('%[^a-z]%',fielda),99),PATINDEX('%[^0-9]%',SUBSTRING(fielda,PATINDEX('%[^a-z]%',fielda),99)),99) AS youroutput
FROM yourtable

Input

yourtable

fielda
SALV3000640PIX32BLU
SALV3334470A9CARBONGRY
TP3000620PIXL128BLK

Output

youroutput
PIX32BLU
A9CARBONGRY
PIXL128BLK

SQL Fiddle:http://sqlfiddle.com/#!6/5722b6/29/0

answered Jul 26 '17 at 09:54

Matt

14,906
27
99
149

This is great! CHEERS! :) – Jacky Montevirgen Jul 26 '17 at 09:56
I have a question with this, for what is `PATINDEX('%[^a-z]%',fielda),99)` what is 99? – Jacky Montevirgen Jul 26 '17 at 10:02
99 is the length of the string they return, i just used 99 as it was longer that anything you needed in your examples but your actual data may be longer. – Matt Jul 26 '17 at 10:04

score 1 · Answer 2 · answered Jul 26 '17 at 10:19

To do this you can use

PATINDEX('%[0-9]%',FieldName)

which will give you the position of the first number, then trim off any letters before this using SUBSTRING or other string functions. (You need to trim away the first letters before continuing with the next step because unlike CHARINDEX there is no starting point parameter in the PATINDEX function).

Then on the remaining string use

PATINDEX('%[a-z]%',FieldName)

to find the position of the first letter in the remaining string. Now trim off the numbers in front using SUBSTRING etc.

You may find this other solution helpful

SQL to find first non-numeric character in a string

score 0 · Answer 3 · answered Jul 26 '17 at 09:53

Try this it may helps you

;With cte (Data)
AS
(
SELECT 'SALV3000640PIX32BLU'    UNION ALL
SELECT 'SALV3334470A9CARBONGRY' UNION ALL
SELECT 'SALV3334470A9CARBONGRY' UNION ALL
SELECT 'SALV3334470B9CARBONGRY' UNION ALL
SELECT 'SALV3334470D9CARBONGRY' UNION ALL
SELECT 'TP3000620PIXL128BLK'   
)
SELECT * , CASE WHEN CHARINDEX('PIX',Data)>0 THEN SUBSTRING(Data,CHARINDEX('PIX',Data),LEN(Data)) 
                WHEN CHARINDEX('A9C',Data)>0 THEN SUBSTRING(Data,CHARINDEX('A9C',Data),LEN(Data))
                ELSE NULL END AS DesiredResult FROM cte

Result

Data                    DesiredResult
-------------------------------------
SALV3000640PIX32BLU     PIX32BLU
SALV3334470A9CARBONGRY  A9CARBONGRY
SALV3334470A9CARBONGRY  A9CARBONGRY
SALV3334470B9CARBONGRY  NULL
SALV3334470D9CARBONGRY  NULL
TP3000620PIXL128BLK     PIXL128BLK

On what basis did you decide that those new rows you added should return `null`? If the OP hasn't provided enough info to answer, it's probably not worth making up new criteria so that you can answer. — underscore_d, Jul 26 '17 at 09:54
i just added the sample query just to get an idea to figure out his requirement ,i didn't said that is exact result what he expects — , Jul 26 '17 at 09:57

Concatenate & Trim String

3 Answers3