SQL拆分字符串
--方法一
--拆分成临时表
create table temp(aaa varchar(20))
declare @strsql as varchar(8000)
select @strsql=''
select @strsql='insert into temp ;values ('''+replace('111;222;333;444;555',';',''') insert into temp ;values (''')+''')'
exec (@strsql)
select * from temp
drop table temp
--方法二
--逐个截取
declare @str varchar(300),@id varchar(300),@m int,@n int
set @str='a,b,c,d,e'
set @m=CHARINDEX(',',@str)
set @n=1
WHILE @m>0
BEGIN
set @id=substring(@str,@n,@m-@n)
print @id
set @n=@m+1
set @m=CHARINDEX(',',@str,@n)
END
--方法三
--SQL中字符串拆分通用函数
CREATE FUNCTION uf_StrSplit
(@origStr varchar(7000), --待拆分的字符串
@markStr varchar(100)) --拆分标记，如','
RETURNS @splittable table
(
str_id varchar(4000) NOT NULL, --编号ID
string varchar(2000) NOT NULL --拆分后的字符串
)
AS
BEGIN
declare @strlen int,@postion int,@start int,@sublen int,@TEMPstr varchar(200),@TEMPid int
SELECT @strlen=LEN(@origStr),@start=1,@sublen=0,@postion=1,@TEMPstr='',@TEMPid=0
if(RIGHT(@origStr,1)<>@markStr )
begin
set @origStr = @origStr + @markStr
end
WHILE((@postion<=@strlen) and (@postion !=0))
BEGIN
IF(CHARINDEX(@markStr,@origStr,@postion)!=0)
BEGIN
SET @sublen=CHARINDEX(@markStr,@origStr,@postion)-@postion;
END
ELSE
BEGIN
SET @sublen=@strlen-@postion+1;
END
IF(@postion<=@strlen)
BEGIN
SET @TEMPid=@TEMPid+1;
SET @TEMPstr=SUBSTRING(@origStr,@postion,@sublen);
INSERT INTO @splittable(str_id,string) values(@TEMPid,@TEMPstr)
IF(CHARINDEX(@markStr,@origStr,@postion)!=0)
BEGIN
SET @postion=CHARINDEX(@markStr,@origStr,@postion)+1
END
ELSE
BEGIN
SET @postion=@postion+1
END
END
END
RETURN
END
