拆分字符串

--功能:拆分字符串

--说明:例如,将下列数据

 

id     id_value

-----------------

1      'aa,bb'

2      'aa,bb,cc'

3      'aaa,bbb,ccc'

 

--转换成以下的格式

id     id_value

-----------------

1      'aa'

1      'bb'

2      'aa'

2      'bb'

2      'cc'

3      'aaa'

3      'bbb'

3      'ccc'

 

--代码-------------------------------------------GO

create table ta

(

    id int,

    id_value varchar(30)

)

 

insert into ta

select 1,'aa,bb' union all

select 2,'aa,bb,cc' union all

select 3,'aaa,bbb,ccc'

 

select

    top 4000 ix = identity(int, 1, 1)

into #tb

from sys.objects a, sys.objects b

 

select

    id,

    id_value = substring(id_value,ix,charindex(',',id_value + ',',ix) - ix)

FROM ta as A, #tb as B

WHERE SUBSTRING( ',' + id_value, ix, 1) = ','

 

drop table ta

drop table #tb