SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


splitting at the comma


splitting at the comma

Author
Message
99posts
99posts
SSC-Enthusiastic
SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)

Group: General Forum Members
Points: 133 Visits: 22
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
GSquared
GSquared
SSC Guru
SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)

Group: General Forum Members
Points: 59013 Visits: 9730
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
99posts
99posts
SSC-Enthusiastic
SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)

Group: General Forum Members
Points: 133 Visits: 22
thanks for the good explanation of everything! Smile

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.
GSquared
GSquared
SSC Guru
SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)

Group: General Forum Members
Points: 59013 Visits: 9730
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
99posts
99posts
SSC-Enthusiastic
SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)

Group: General Forum Members
Points: 133 Visits: 22
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.
GSquared
GSquared
SSC Guru
SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)

Group: General Forum Members
Points: 59013 Visits: 9730
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
99posts
99posts
SSC-Enthusiastic
SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)

Group: General Forum Members
Points: 133 Visits: 22
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.
99posts
99posts
SSC-Enthusiastic
SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)

Group: General Forum Members
Points: 133 Visits: 22
Gsquared... that did the trick!

you guys really know your craft! THANKS!!
GSquared
GSquared
SSC Guru
SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)

Group: General Forum Members
Points: 59013 Visits: 9730
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
gareth.bowen
gareth.bowen
Valued Member
Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)

Group: General Forum Members
Points: 70 Visits: 415
Here's a link to a function that splits on any string up to 4 characters in length.

[url=http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=77523][/url]
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search