Jacky Montevirgen Jacky Montevirgen - 3 years ago 69
SQL Question

Concatenate & Trim String

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

Answer Source

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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download