This was a question on the microsoft.public.sqlserver.programming newsgroup, I thought it would be interesting to you to see wat i answered to this one
I believe that I have never used this many parenthese in my life before in a simple 2 column split
This is the question:
I have a column in a table that has multiple pieces of information in it that
I need to break out into various columns. The column is random but the
values I need to separate out are the number and the UN number as below:
245 HELIUM, COMPRESSED 2.2 UN1046
I need to separate the 2.2 and the UN1046 into different columns. How do I
parse this?
Here is the link to the original question at the microsoft.public.sqlserver.programming newsgroup
And here is my solution
CREATE TABLE Inventory (ItemDescription VARCHAR(99))
INSERT Inventory VALUES ('245 HELIUM, COMPRESSED 2.2 UN1046' )
INSERT Inventory VALUES ('24adada5 HELIsadasdadUM, sdsdsd 6.6 UN99' )
INSERT Inventory VALUES ('24adada5 HELIsadasdadUM, sdsdsd 446.6777 UN9988888' )
INSERT Inventory VALUES ('24adada5 HEdUM, sdsdsd 446.0 UN9988' )
SELECT RIGHT(ItemDescription,PATINDEX('% %',
REVERSE(ItemDescription))-1) AS COL1,
LTRIM(REVERSE(LEFT(REVERSE(LEFT(ItemDescription,(LEN(ItemDescription)-PATINDEX('% %', REVERSE(ItemDescription))))),
PATINDEX('% %',REVERSE(LEFT(ItemDescription,(LEN(ItemDescription)-PATINDEX('% %', REVERSE(ItemDescription)))))))))
AS COL2
FROM Inventory
i feel for you , denis ;o)
ReplyDeletelook at how easy it is in mysql --
select substring_index(substring_index(ItemDescription,' ',-2),' ',1) as Col1
, substring_index(substring_index(ItemDescription,' ',-2),' ',-1) as Col2
from Inventory
rudy
r937.com
Nested sub-selects would help manage the chaos with a repeated pattern for each additional column that was parsed.
ReplyDeleteSELECT REVERSE(COL1) COL1,
REVERSE(LEFT(COL2, CHARINDEX(' ', COL2)-1)) COL2
FROM
(SELECT LEFT(COL, CHARINDEX(' ', COL)-1) COL1
,LTRIM(SUBSTRING(COL, CHARINDEX(' ', COL), LEN(COL))) COL2
FROM (SELECT REVERSE(ItemDescription) COL FROM Inventory) A
) A