quote:

---

Originally posted by lcpx

Hi all,

I have a table structure as below:

create table t (
code varchar(8),
data varchar(2000));

insert into t values ('A', '123,456,789');
insert into t values ('B', '012,345');
insert into t values ('C', '678,901,234');

commit;

A 123,456,789
B 012,345
C 678,901,234



I want the final displat like this:

CODE COLUMN_V
-------- --------
A 123
A 456
A 789
B 012
B 345
C 678
C 901
C 234

So I created a function to parse the CSV string, the function works.

create function fn_ParseCSVString
(
@CSVString varchar(8000) ,
@Delimiter varchar(10)
)
returns @tbl table (s varchar(1000))
as
/*
select * from dbo.fn_ParseCSVString ('qwe,c,rew,c,wer', ',c,')
*/
begin
declare @i int ,
@j int
select @i = 1
while @i <= len(@CSVString)
begin
select @j = charindex(@Delimiter, @CSVString, @i)
if @j = 0
begin
select @j = len(@CSVString) + 1
end
insert @tbl select substring(@CSVString, @i, @j - @i)
select @i = @j + len(@Delimiter)
end
return
end

But I can't use it properly, I wrote the below sql query but it doesn't work, very confusing. Can anybody help me??

SQL test:

select t.code,
v.*
from t,
fn_ParseCSVString(t.data,'-') v;


Cheers,
Ken

---