CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Avinash
Avinash

Reputation: 543

Extract number from certain strings in sql server

I have a text like below

AB-MFG 49UN STEEL
GH-NH, 345UN IRON
M787 UH, 77 UNITS GREY
G7-YUT 365 UNITS WHITE

and i want to extract the numbers before string UN and UNITS

+------------------------+-----+
| AB-MFG 49UN STEEL      | 49  |
+------------------------+-----+
| GH-NH, 345UN IRON      | 345 |
+------------------------+-----+
| M787 UH, 77 UNITS GREY | 77  |
+------------------------+-----+
| G7-YUT 365 UNITS WHITE | 365 |
+------------------------+-----+

Currently I'm using belong query. I have to write a case statement to cover both cases. Are there any alternate efficient solution for this?

SELECT REVERSE(SUBSTRING(SUBSTRING(REVERSE(LEFT(desc, CHARINDEX('UN ', desc))), 2, 1000), 1, CHARINDEX(' ', SUBSTRING(REVERSE(LEFT(desc, CHARINDEX('UN ', desc))), 2, 1000))))
FROM MYTABLE

Upvotes: 0

Views: 80

Answers (2)

Arun Palanisamy
Arun Palanisamy

Reputation: 5459

You can use PATINDEX with CROSS APPLY. I see a pattern that your required number is always appearing after the space and before your keyword. If that is the case, you can use the below code.

SELECT DESC, 
       CASE WHEN UN.I !=0 THEN SUBSTRING(DESC,NUM.I+1,UN.I-NUM.I-1)
            WHEN UNITS.I !=0 THEN SUBSTRING(DESC,NUM.I+1,UNITS.I-NUM.I-1)
            ELSE ''
       END AS REQUIRED_NUM
FROM MYTABLE
CROSS APPLY (VALUES(PATINDEX('%UN %', DESC)))UN(I)
CROSS APPLY (VALUES(PATINDEX('% UNITS%',DESC)))UNITS(I)
CROSS APPLY (VALUES(PATINDEX('% [0-9]%', DESC)))NUM(I)

Please note that desc is a keyword in sql server. You should avoid using that for a column name.

Upvotes: 1

GuidoG
GuidoG

Reputation: 12014

you could use an IIF in stead of the case

declare @mytable table (value varchar(100))

insert into @mytable values('AB-MFG 49UN STEEL'), 
                           ('GH-NH, 345UN IRON'),
                           ('M787 UH, 77 UNITS GREY'),
                           ('G7-YUT 365 UNITS WHITE')

select IIF(CharIndex('un ', value) = 0, CharIndex('units', value), CharIndex('un ', value))
from   @mytable

this will return the position of the UN and the UNITS
From there you can do all the rest again.

Its not much better, but its an alternative for the case at least

EDIT

the full query would look like this than

declare @mytable table (value varchar(100))

insert into @mytable values('AB-MFG 49UN STEEL'), 
                           ('GH-NH, 345UN IRON'),
                           ('M787 UH, 77 UNITS GREY'),
                           ('G7-YUT 365 UNITS WHITE')

select reverse(left(ltrim(SUBSTRING(REVERSE(LEFT(value, t.PosValue)), 2, 1000)), charindex(' ', ltrim(SUBSTRING(REVERSE(LEFT(value, t.PosValue)), 2, 1000)))))
from   ( select value,
                IIF(CharIndex('un ', value) = 0, CharIndex('units', value), CharIndex('un ', value)) as PosValue
         from   @mytable
       ) t

and the result is this

COLUMN1
49 
345 
77 
365

Upvotes: 0

Related Questions