从sql server中的某些字符串中提取数字

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

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

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

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

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

COLUMN1
365

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)

从sql server中的某些字符串中提取数字

2 个回答