use tempdb go --测试数据 declare @s varchar(1000) set @s='ak47,mp5,1,23' /*要求输出结果 S ---- ak47 mp5 1 23 */ --3种方法对比: --1.[朴实]动态Exec方法: declare @s1 varchar(1000) set @s1=right(replace(','+@s,',',''' as S union select '''),len(replace(','+@s,',',''' as S union select '''))-12)+'''' exec(@s1) --2.[变通]表交叉方法: select replace(reverse((left(s,charindex(',',s)))),',','') as S from( select r,reverse(left(@s,r))+',' as s from( select (select count(*) from sysobjects where name<=t.name ) as r from sysobjects t )a where r<=len(@s) and left(@s+',',r+1) like '%,' )t order by r --3.[高级]XML方法: DECLARE @idoc int; DECLARE @doc xml; set @doc=cast('<Root><item><S>'+replace(@s,',','</S></item><item><S>')+'</S></item></Root>' as xml) EXEC sp_xml_preparedocument @Idoc OUTPUT, @doc SELECT * FROM OPENXML (@Idoc, '/Root/item',2) WITH ( [S] varchar(10) ) 来源
declare @a varchar(20)='1,2,3,4,5',@sql varchar(1000) set @sql='select '+REPLACE(@a,',',' as A union select ') exec(@sql)/* A ----------- 1 2 3 4 5(5 行受影响)
if object_id('A','U') is not null drop table A go create table A ( col varchar(10) ) go insert into A select '1,2,3,4,5' go select substring(col,number,charindex(',',col+',',number)-number) from A cross join master..spt_values where type='p' and number between 1 and len(col) and substring(','+col,number,1)=',' go /* ---------- 1 2 3 4 5(5 行受影响)*/
有没有SQL里面用的?我是要写在C++程序里面的
select A=SUBSTRING(a.A,b.number,CHARINDEX(',',a.A+',',b.number)-b.number) from 表 a join master..spt_values b on b.type='p' and b.number <=len(a.A) where CHARINDEX(',',','+a.A,b.number)=b.number
go
--测试数据
declare @s varchar(1000)
set @s='ak47,mp5,1,23'
/*要求输出结果
S
----
ak47
mp5
1
23
*/
--3种方法对比:
--1.[朴实]动态Exec方法:
declare @s1 varchar(1000)
set @s1=right(replace(','+@s,',',''' as S union select '''),len(replace(','+@s,',',''' as S union select '''))-12)+''''
exec(@s1)
--2.[变通]表交叉方法:
select replace(reverse((left(s,charindex(',',s)))),',','') as S from(
select r,reverse(left(@s,r))+',' as s
from(
select (select count(*) from sysobjects where name<=t.name ) as r
from sysobjects t
)a where r<=len(@s)
and left(@s+',',r+1) like '%,'
)t order by r
--3.[高级]XML方法:
DECLARE @idoc int;
DECLARE @doc xml;
set @doc=cast('<Root><item><S>'+replace(@s,',','</S></item><item><S>')+'</S></item></Root>' as xml)
EXEC sp_xml_preparedocument @Idoc OUTPUT, @doc
SELECT * FROM OPENXML (@Idoc, '/Root/item',2)
WITH (
[S] varchar(10)
) 来源
set @sql='select '+REPLACE(@a,',',' as A union select ')
exec(@sql)/*
A
-----------
1
2
3
4
5(5 行受影响)
if object_id('A','U') is not null
drop table A
go
create table A
(
col varchar(10)
)
go
insert into A select '1,2,3,4,5'
go
select substring(col,number,charindex(',',col+',',number)-number) from A cross join master..spt_values where type='p' and number between 1 and len(col) and substring(','+col,number,1)=','
go
/*
----------
1
2
3
4
5(5 行受影响)*/
from 表 a join master..spt_values b on b.type='p' and b.number <=len(a.A)
where CHARINDEX(',',','+a.A,b.number)=b.number