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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bill PrewIT / Software Engineering ConsultantCommented:
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
Bill PrewIT / Software Engineering ConsultantCommented:
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
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

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
Bill PrewIT / Software Engineering ConsultantCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.