t-sql extract characters to the left and right of specific character

I have the following data example:
text 1.234 X 5.678 abc
text 1.234X5.678 abc
text 1.2X 5.6 abc
text 1.2 X 5.6 abc
text 1X2 abc
text 1 X 2 abc

From this data I need to find the position of the "X" (always an uppercase "X") and get the data to the left and the data to the right.   The data to the left is the column named "width" and the data to the right is named "length"

After complete the results should look like this:
text 1.234 X 5.678 abc --> width = 1.234, length = 5.678
text 1.234X5.678 abc --> width = 1.234, length = 5.678
text 1.2X 5.6 abc --> width = 1.2, length = 5.6
text 1.2 X 5.6 abc --> width = 1.2, length = 5.6
text 1X2 abc --> width = 1, length = 2
text 1 X 2 abc --> width = 1, length = 2

Any help is appreciated.
maverick0728Asked:
Who is Participating?
 
Bill PrewCommented:
Okay, give this a try, seems to work in tests here.

CASE CHARINDEX(' ', REVERSE(RTRIM(LEFT(COL1, CHARINDEX('X', COL1 COLLATE Latin1_General_CS_AS)-1)))) WHEN 0 THEN RTRIM(LEFT(COL1, CHARINDEX('X', COL1 COLLATE Latin1_General_CS_AS)-1)) ELSE REVERSE(LEFT(REVERSE(RTRIM(LEFT(COL1, CHARINDEX('X', COL1 COLLATE Latin1_General_CS_AS)-1))), CHARINDEX(' ', REVERSE(RTRIM(LEFT(COL1, CHARINDEX('X', COL1 COLLATE Latin1_General_CS_AS)-1))))-1)) END AS Width,
CASE CHARINDEX(' ', LTRIM(SUBSTRING(COL1, CHARINDEX('X', COL1 COLLATE Latin1_General_CS_AS)+1, 999))) WHEN 0 THEN LTRIM(SUBSTRING(COL1, CHARINDEX('X', COL1 COLLATE Latin1_General_CS_AS)+1, 999)) ELSE LEFT(LTRIM(SUBSTRING(COL1, CHARINDEX('X', COL1 COLLATE Latin1_General_CS_AS)+1, 999)), CHARINDEX(' ', LTRIM(SUBSTRING(COL1, CHARINDEX('X', COL1 COLLATE Latin1_General_CS_AS)+1, 999)))-1) END AS Length

Open in new window


»bp
0
 
Bill PrewCommented:
Try these in your SELECT query, to pull out the sub text as the new column names.

trim(left(col1, charindex('X', col1)-1)) as Width
trim(mid(col1, charindex('X', col1)+1, 999)) as Length

Open in new window


»bp
0
 
maverick0728Author Commented:
trim and mid are not valid on ms sql server
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Bill PrewCommented:
Ah yes, too many flavors of SQL.  We can use LTRIM() and RTRIM() and SUBSTRING(), but I just noticed I missed a big detail.  You have text before and after the numbers, I was happily ignoring that detail.  So need to think a bit more about that...

Will there always be some text before and after the numbers, separated by one space?  Or could there be just the numbers, or more than one space between text and numbers?


»bp
0
 
maverick0728Author Commented:
It may just be the numbers like:
1.234x5.678
1.234 x 5.678

it is always " X " 
but there may be more than one space before/after:  
some text    1.234X5.678  some text
0
 
Mark WillsTopic AdvisorCommented:
ah, well, not quick enough...
select 'width = '+replace(replace(substring(YourColumn,patindex('%[0-9.]%',YourColumn),len(YourColumn)+2 - (patindex('%[0-9.]%',YourColumn) + patindex('%[0-9.]%',reverse(YourColumn)))),' ',''),'X',', length = ')
from YourTable

Open in new window


Probably best as a user defined function...
answer already accpeted

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.