当前位置: 动力学知识库 > 问答 > 编程问答 >

sql - Extract size (numeric) from string

问题描述:

I have column in a table with values stored as

HX CAP SCR GD5 1/2-13 X 3 3/4

HX CAP SCR GD8 1/2-13 X 4 1/4

HX CAP SCR Grade 5 1/2-13 X 5 1/2

HX CAP SCR Grade 8 1/2-13 X 6 1/2

Now, I need to store these values to another table like

CATEGORY SIZE

HX CAP SCR GD5 1/2-13 X 3 3/4

HX CAP SCR GD8 1/2-13 X 4 1/4

HX CAP SCR Grade 5 1/2-13 X 5 1/2

HX CAP SCR Grade 8 1/2-13 X 6 1/2

I need to separate the Category from the Size values.

This is what I have so far, but it is not working the GD5 and GR8 are part of the category column.

SELECT CustDesc

,'??????' as Category

,SUBSTRING(CustDesc,PatIndex('%[0-9.-]%', CustDesc),8000) as Size

FROM [##CustParts]

Result from above query

CustDesc Category Size

HX CAP SCR GD5 1/2-13 X 3 ?????? 5 1/2-13 X 3

Plz let me know what is the query to do this....

网友答案:

You need to make up a rule that seems reasonable. For example, it looks like the size starts right after the first space before " X". We can get this use the (primitive) string functions in SQL Server:

select ltrim(rtrim(left(val, pos1 - spacebefore))) as product,
     substring(val, pos1 - spacebefore + 1, 1000) as size
from (select t.*, charindex(' ', reverse(left(val, pos1-1))) as spacebefore
      from (select t.*,
                   charindex(' X ', val) as pos1
            from (select 'abce 15/3 x 2' as val) t
           ) t
     ) t
网友答案:

based upon your example it looks the number 1 is your reference to parse the value in each tuple. research tsql's SQL CHARINDEX Command - Find Position of Text in a String

are there more values to be parsed? Please provide a better cross-section of data to be parsed. - thx

网友答案:

Hi if your string always contains the character '1/2',then this would work perfectly.

CREATE TABLE #tbl(col1 varchar(200))

INSERT INTO #tbl 
VALUES('HX CAP SCR GD5 1/2-13 X 3 3/4'),
('HX CAP SCR GD8 1/2-13 X 4 1/4'),
('HX CAP SCR Grade 5 1/2-13 X 5 1/2'),
('HX CAP SCR Grade 8 1/2-13 X 6 1/2')

select Left(col1,CHARINDEX('1/2',col1,1)-1),RIGHT(col1,len(col1)-(CHARINDEX('1/2',col1,1)-1)) from #tbl
分享给朋友:
您可能感兴趣的文章:
随机阅读: