SQL Server 2008 Split 1 column into 3 (Shipping dimensions) -

i have table with 1 column product dimensions.

example rows: 16" l x 22" w x 6" h 22.5" l x 12" w x 9" h

i trying length, width, , height separate columns. have use sql because being used in software integration accepts sql statements. thinking have go route of regex.

sql statement data far
select top 10 [id] ,substring([ship_dimensions],patindex('%[0-9]\"%',[ship_dimensions]),2) length ,substring([ship_dimensions],patindex('%[0-9]\"%',[ship_dimensions]),2) width ,substring([ship_dimensions],patindex('%[0-9]*\"%',[ship_dimensions]),2) height [part]

i need output length | width | height 16 | 22 | 6 22.5 | 12 | 9

any suggestions appreciated.

one way follows:

select     left(dim, charindex('" l', dim)-1) [length] ,   substring(dim, charindex('" l', dim)+6, charindex('" w', dim)-charindex('" l x ', dim) - 6) [width] ,   substring(dim, charindex('" w', dim)+6, charindex('" h', dim)-charindex('" w x ', dim) - 6) [height] test 

the idea markers have in text, , use them parcel out string substrings. approach rigid, in assumes pattern shown in example followed precisely in records, i.e. markers present, along spaces. there implicit assumption dimensions in inches. can vary width of columns.

demo.

note: assuming dealing legacy database, there no way right thing (which separate out dimensions separate columns).