--创建拆分函数 CREATE FUNCTION f_splitSTR( @s varchar(8000), --待分拆的字符串 @split varchar(10) --数据分隔符 )RETURNS @re TABLE(col varchar(100)) AS BEGIN DECLARE @splitlen int SET @splitlen=LEN(@split+'a')-2 WHILE CHARINDEX(@split,@s)>0 BEGIN INSERT @re VALUES(LEFT(@s,CHARINDEX(@split,@s)-1)) SET @s=STUFF(@s,1,CHARINDEX(@split,@s)+@splitlen,'') END INSERT @re VALUES(@s) RETURN END GO--调用函数 select * from dbo.f_splitstr('a,b,c,ef,sss',',')
create function f_split(@c varchar(2000),@split varchar(2)) returns @t table(col varchar(20)) as begin
while(charindex(@split,@c)<>0) begin insert @t(col) values (substring(@c,1,charindex(@split,@c)-1)) set @c = stuff(@c,1,charindex(@split,@c),'') end insert @t(col) values (@c) return end go
select * from dbo.f_split('dfkd,dfdkdf,dfdkf,dffjk',',')
drop function f_split
col -------------------- dfkd dfdkdf dfdkf dffjk
(所影响的行数为 4 行)
select * from f_split('a,b,c,ef,sss',',') /* a b c ef sss */CREATE function f_split(@str varchar(8000),@StrSeprate varchar(1)) returns @temp table(a varchar(100)) as begin declare @i int set @str=rtrim(ltrim(@str)) set @i=charindex(@StrSeprate,@str) while @i>=1 begin insert @temp values(left(@str,@i-1)) set @str=substring(@str,@i+1,len(@str)-@i) set @i=charindex(@StrSeprate,@str) end if @str<>'\' insert @temp values(@str) return end
--创建拆分函数
CREATE FUNCTION f_splitSTR(
@s varchar(8000), --待分拆的字符串
@split varchar(10) --数据分隔符
)RETURNS @re TABLE(col varchar(100))
AS
BEGIN
DECLARE @splitlen int
SET @splitlen=LEN(@split+'a')-2
WHILE CHARINDEX(@split,@s)>0
BEGIN
INSERT @re VALUES(LEFT(@s,CHARINDEX(@split,@s)-1))
SET @s=STUFF(@s,1,CHARINDEX(@split,@s)+@splitlen,'')
END
INSERT @re VALUES(@s)
RETURN
END
GO--调用函数
select * from dbo.f_splitstr('a,b,c,ef,sss',',')
create function f_split(@c varchar(2000),@split varchar(2))
returns @t table(col varchar(20))
as
begin
while(charindex(@split,@c)<>0)
begin
insert @t(col) values (substring(@c,1,charindex(@split,@c)-1))
set @c = stuff(@c,1,charindex(@split,@c),'')
end
insert @t(col) values (@c)
return
end
go
select * from dbo.f_split('dfkd,dfdkdf,dfdkf,dffjk',',')
drop function f_split
col
--------------------
dfkd
dfdkdf
dfdkf
dffjk
(所影响的行数为 4 行)
select * from f_split('a,b,c,ef,sss',',')
/*
a
b
c
ef
sss
*/CREATE function f_split(@str varchar(8000),@StrSeprate varchar(1))
returns @temp table(a varchar(100))
as
begin
declare @i int
set @str=rtrim(ltrim(@str))
set @i=charindex(@StrSeprate,@str)
while @i>=1
begin
insert @temp values(left(@str,@i-1))
set @str=substring(@str,@i+1,len(@str)-@i)
set @i=charindex(@StrSeprate,@str)
end
if @str<>'\'
insert @temp values(@str)
return
end