Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

splitting at the comma Expand / Collapse
Author
Message
Posted Tuesday, November 24, 2009 1:53 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, August 31, 2010 4:54 PM
Points: 11, 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
Post #824136
Posted Tuesday, November 24, 2009 2:23 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, December 18, 2014 9:58 AM
Points: 13,872, Visits: 9,600
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
Post #824148
Posted Tuesday, November 24, 2009 2:33 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, August 31, 2010 4:54 PM
Points: 11, Visits: 22
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.

Post #824152
Posted Tuesday, November 24, 2009 2:38 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, December 18, 2014 9:58 AM
Points: 13,872, Visits: 9,600
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
Post #824157
Posted Tuesday, November 24, 2009 2:45 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, August 31, 2010 4:54 PM
Points: 11, 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.
Post #824163
Posted Tuesday, November 24, 2009 2:50 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, December 18, 2014 9:58 AM
Points: 13,872, Visits: 9,600
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
Post #824165
Posted Tuesday, November 24, 2009 2:54 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, August 31, 2010 4:54 PM
Points: 11, 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.
Post #824170
Posted Tuesday, November 24, 2009 3:00 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, August 31, 2010 4:54 PM
Points: 11, Visits: 22
Gsquared... that did the trick!

you guys really know your craft! THANKS!!

Post #824174
Posted Wednesday, November 25, 2009 7:08 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, December 18, 2014 9:58 AM
Points: 13,872, Visits: 9,600
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
Post #824512
Posted Thursday, November 26, 2009 2:21 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, October 3, 2014 4:18 AM
Points: 19, Visits: 398
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]

Post #825094
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse