Author |
Topic |
AskSQLTeam
Ask SQLTeam Question
0 Posts |
Posted - 2005-08-31 : 07:36:14
|
RAJ writes "Hi How do i find a sencond ","(comma) in the following String1102,3456,,4566I need the select statement to find out the position of second ","and third ","Thanks in AdvanceRaj" |
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-08-31 : 07:57:00
|
Not sure whether this is what you wanteddeclare @s varchar(100)Set @s='1102,3456,,4566'Select Charindex(',',substring(@s,charindex(',',@s)+1,len(@s)))+charindex(',',@s)MadhivananFailing to plan is Planning to fail |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-08-31 : 10:10:24
|
In case you are interested in parsing a comma seperated value string, do a forum search as well as a sight search on "parse csv". More than a few methods have been posted.Here's a technique I've used before:set nocount ondeclare @csv varchar(7990) ,@idx intdeclare @vals table (val int)select @csv = '1102,3456,,4566' ,@idx = 1set @csv = ',' + @csv + ','while @idx between 1 and len(@csv)-1begin insert @vals select v from ( select substring(@csv, @idx+1, charindex(',', @csv, @idx+1)-@idx-1) v ) a where len(v) > 0 --to insure only integers are returned and v not like '%[^0-9]%' set @idx = charindex(',', @csv, @idx+1)endselect * from @vals EDIT:This is assuming you only want to parse integers. It can be easily adapted to allow any string to be parsed. You'd just remove the one where clause that limits to 0-9 and change the datatype in the table variable.Be One with the OptimizerTG |
|
|
VIG
Yak Posting Veteran
86 Posts |
Posted - 2005-08-31 : 14:24:00
|
[code]declare @s varchar(100)Set @s='1102,3456,,4566'select charindex(',',@s,charindex(',',@s)+1)[/code] |
|
|
Igor2004
More clever than you
78 Posts |
Posted - 2005-09-01 : 00:00:34
|
select dbo.AT('1102,3456,,4566', ',', 2)http://www.universalthread.com/wconnect/wc.dll?LevelExtreme~2,54,33,27115 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-09-01 : 00:58:59
|
TG, yours is a good workVIG, yours is simpler than mine. Thats coolMadhivananFailing to plan is Planning to fail |
|
|
|
|
|