How to find all commas positions in the string? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

How to find all commas positions in the string?

Author

Topic

zubamark
Starting Member

23 Posts

Posted - 2006-03-24 : 13:34:15

Hi,

Can you help

I have a string that has multiple commas:
'AAA,BBB,CCC,DDD,EEE,...'
I need to pick any characters between commas or find all commas positions.

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-03-24 : 14:02:37

>> any characters between commas
U mean split the string to several and display as different rows?

>>find all commas positions
How do u want the output to be?
U mean
4,8,12,16,20
or
4
8
12
16
20

or what ?

Give the out put u expect - with respect to the sample

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-03-24 : 15:13:05

see this
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=63664

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-24 : 18:40:44

use the function here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648

KH
Choice is an illusion, created between those with power, and those without.
Concordantly, while your first question may be the most pertinent, you may or may not realize it is also the most irrelevant

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-03-27 : 13:38:22

--input
declare @s varchar(100)
set @s = 'AAA,BBB,CCC,DDD,EEE,...'

--numbers table
DECLARE @numbers TABLE (i INT IDENTITY(1, 1), c char(1), marker tinyint)
INSERT @numbers SELECT TOP 101 NULL, 0 FROM master.dbo.syscolumns

--comma positions
select i from @numbers where substring(@s, i, 1) = ','

--characters between commas
select substring(@s, i, charindex(',', substring(@s+',', i, 100))-1)
from @numbers where substring(',' + @s, i, 1) = ','

--rolled up comma positions
declare @t varchar(100)
set @t = ''
select @t = @t + cast(i as varchar(10)) + ',' from @numbers where substring(@s, i, 1) = ','
print left(@t, len(@t)-1)

I need more restraint!

Ryan Randall
www.monsoonmalabar.com

Ideas are easy. Choosing between them is the hard part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources