Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 How to find the second comma in a given string

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 String
1102,3456,,4566

I need the select statement to find out the position of second ","
and third ","

Thanks in Advance

Raj"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-31 : 07:57:00
Not sure whether this is what you wanted

declare @s varchar(100)
Set @s='1102,3456,,4566'
Select Charindex(',',substring(@s,charindex(',',@s)+1,len(@s)))+charindex(',',@s)



Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

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 on
declare @csv varchar(7990)
,@idx int

declare @vals table (val int)

select @csv = '1102,3456,,4566'
,@idx = 1

set @csv = ',' + @csv + ','

while @idx between 1 and len(@csv)-1
begin
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)
end

select * 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 Optimizer
TG
Go to Top of Page

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]
Go to Top of Page

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
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-01 : 00:58:59
TG, yours is a good work
VIG, yours is simpler than mine. Thats cool


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
   

- Advertisement -