Extract number from certain strings in sql server

Question

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

What do you mean by "too big"? Its OK for the query to have a few characters. — Dale K
– Dale K, Commented Apr 7, 2021 at 9:43
That's right.I will remove that statement. I'm looking for alternate solution that covers both cases and efficient than this. @DaleK — Avinash
– Avinash, Commented Apr 7, 2021 at 9:47

Arun Palanisamy · Accepted Answer · 2021-04-08 13:17:53Z

1

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.

edited Apr 8, 2021 at 13:17

answered Apr 8, 2021 at 13:08

Arun Palanisamy

5,4898 gold badges33 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

GuidoG · Accepted Answer · 2021-04-07 12:15:12Z

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

Collectives™ on Stack Overflow

Extract number from certain strings in sql server

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related