November 24, 2009 at 1:53 pm
ok... new to splitting strings
found this really good sample.
http://sqlservercodebook.blogspot.com/2008/03/how-to-split-column-in-sql.html
i can see what is going on here, but not good enough with the functions ( which looks complicated as hell ),
but wondering if this can be modified to ONLY separate the string on each side of the comma.
left side col1, and right side col2. does this make sense?
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
so for example the first row would go from this:
245 HELIUM, COMPRESSED 2.2 UN1046
to results like this:
[col1]...................[col2]..........................
245 HELIUM...........COMPRESSED 2.2 UN1046
November 24, 2009 at 2:23 pm
Someone went WAY overboard on that split.
If it'll always be just one comma, try something like this:
declare @String varchar(100);
select @String = 'first,last';
select left(@String, charindex(',', @String)-1),
right(@String, len(@String)-charindex(',', @String));
Charindex finds the position of the comma in the string.
Left pulls everything left of the indicated position, which is based on the Charindex of the comma.
Right pulls everything to the right of the indicated position, which is the length of the string minus the position of the comma.
(Both left and right pull a certain number of characters.)
If you use column names instead of the variable, and a From clause with your table in it, it'll work on that instead.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 24, 2009 at 2:33 pm
thanks for the good explanation of everything! 🙂
hopefully just one more question, and i will be done.
according to your example ( which is much easier to read ) than my previous post...
could i simply replace the 'first,last' with the column name which has the string values?
i just tried it like this:
declare @String varchar(100);
select @String = 'MyColumn'
select left(@String, charindex(',', @String)-1),
right(@String, len(@String)-charindex(',', @String))
from [MyTable]
then i get a SUBSTRING error message which is weird cause i don't see the SUBSTRING function
in there at all.
Msg 536, Level 16, State 5, Line 5
Invalid length parameter passed to the SUBSTRING function.
November 24, 2009 at 2:38 pm
select left(@String, charindex(',', @String)-1) as Col1,
right(@String, len(@String)-charindex(',', @String)) as Col2;
Replace "@String" with your column name, and add "From " and your table name. Replace "Col1" and "Col2" with your desired column names.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 24, 2009 at 2:45 pm
thanks again gsquared.
really grateful of your help, and especially since the code is getting alot shorter.
ran the script, but getting the SUBSTRING error again.
just did some reading up on this error, and found this:
Causes:
This error is caused by passing a negative value to the length parameter of the SUBSTRING, LEFT and RIGHT string functions. This usually occurs in conjunction with the CHARINDEX function wherein the character being searched for in a string is not found and 1 is subtracted from the result of the CHARINDEX function.
LEFT(@String, CHARINDEX(' ', @String) - 1)
If the character is not found in a string, a space in this example, the CHARINDEX function will return a value of 0. Subtracting 1 to this will become -1 and using this as the length parameter in the SUBSTRING or LEFT functions will result to this error.
November 24, 2009 at 2:50 pm
Add:
Where YourColumnName like '%,%'
to the end of the query.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 24, 2009 at 2:54 pm
we are getting alot closer.
i simply replaced the -1 with -0
the columns are both there, and string has been separated, but... the comma (,) remains
on the end of col1, but still much much better than before.
i will also change this as you specified with the where...%,% and try it agian.
November 24, 2009 at 3:00 pm
Gsquared... that did the trick!
you guys really know your craft! THANKS!!
November 25, 2009 at 7:08 am
The -1 is what gets rid of the comma. Leave it in.
Glad you got what you need, and thank you for the compliment. 🙂
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 26, 2009 at 2:21 am
November 30, 2009 at 7:05 am
gareth.bowen (11/26/2009)
Here's a link to a function that splits on any string up to 4 characters in length.
If what was needed was a string parser with multiple delimiters, I'd have gone that route. But what he's looking for is something that splits it once. More efficient this way.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply