Extract size (numeric) from string

Question

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....

Answer 1

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