, delemeter - SQL Server Forums

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

Transact-SQL (2000)

, delemeter

Author

Topic

KENI
Starting Member

6 Posts

Posted - 2009-02-27 : 21:10:43

I Have table like this

[INITIALS]
ABC,CC,DD,AA,AC
XX,ABM
ACC,RRRM
RLM,RLM,RLM,RLM,RLM,RLM
BEL,TEL,XEL,TEL,CEL,MEL,TEL,TEL,TEL,EL,EL,XLM,RM,RM,RLM,RLM,RLM,RLM,RLMR

I want a result like this

[INITIALS]
ABC
CC
DD
AA
AC
XX
ABM
ACC
RRRM
RLM
RLM
RLM
RLM
RLM
RLM
BEL
TEL
XEL
TEL
CEL
MEL
TEL
TEL
TEL
EL
EL
XLM
RM
RM

thanks in advance...hope you can help guys.

sridhar.dbe
Starting Member

34 Posts

Posted - 2009-02-27 : 23:15:45

declare @str table(Col2 varchar(100))
insert into @str
select 'ABC,CC,DD,AA,AC' union all
select 'XX,ABM' union all
select 'ACC,RRRM' union all
select 'RLM,RLM,RLM,RLM,RLM,RLM' union all
select 'BEL,TEL,XEL,TEL,CEL,MEL,TEL,TEL,TEL,EL,EL,XLM,RM,RM,RLM,RLM,RLM,RLM,RLMR'

SELECT
replace(SUBSTRING(s.Col2,charindex(',',s.Col2,v.number),abs(charindex(',',s.Col2,charindex(',',s.Col2,v.number)+1)-charindex(',',s.Col2,v.number))),',','')as value
FROM @str s
INNER JOIN master..spt_values AS v on v.Type = 'P'
AND v.number > 0
AND v.number <= len(s.Col2)
WHERE substring(',' + s.Col2, v.number, 1) = ','

OR

SELECT
SUBSTRING(col2, v.Number - 1,
COALESCE(NULLIF(CHARINDEX(',', col2, v.Number), 0), LEN(col2) + 1) - v.Number + 1) AS value
FROM @str AS s
INNER JOIN master..spt_values AS v ON v.Type = 'p'
WHERE SUBSTRING(',_' + col2, v.Number, 1) = ','
isk

KENI
Starting Member

6 Posts

Posted - 2009-02-28 : 00:59:56

sridhar,

thank you so much.. your expertise and help have been invaluable during this process.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources