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.
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.
ASKER
trim and mid are not valid on ms sql server
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
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
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ah, well, not quick enough...
Probably best as a user defined function...
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 YourTableProbably best as a user defined function...
answer already accpeted
Open in new window
»bp