SQL Server分割字符串函数
create function F_split( |
@s varchar(8000), --包含多个数据项的字符串 |
@pos int, --要获取的数据项的位置 |
@split varchar(10) --数据分隔符 |
)RETURNS varchar(100) |
AS |
BEGIN |
IF @s IS NULL RETURN(NULL) |
DECLARE @splitlen int --分隔符长度 |
SELECT @splitlen=LEN(@split+'a')-2 |
WHILE @pos>1 AND charindex(@split,@s+@split)>0 |
SELECT @pos=@pos-1, |
@s=stuff(@s,1,charindex(@split,@s+@split)+@splitlen,'') |
RETURN(nullif(left(@s,charindex(@split,@s+@split)-1),'')) |
END |
GO |
declare @s nvarchar(1000) |
set @s ='aa/bb/cc/dd/ee/ff' |
select dbo.F_split(@s,1,'/'),dbo.F_split(@s,2,'/'),dbo.F_split(@s,3,'/') |
IF EXISTS ( SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE SPECIFIC_NAME = N'Split' ) |
DROP FUNCTION Split |
GO |
CREATE FUNCTION dbo.Split |
( |
@ItemList VARCHAR(4000), |
@delimiter VARCHAR(10) |
) |
RETURNS @IDTable TABLE (Item VARCHAR(50)) |
AS |
BEGIN |
WHILE CHARINDEX(@delimiter, @ItemList)>0 |
BEGIN |
INSERT @IDTable SELECT LEFT(@ItemList,CHARINDEX(@delimiter,@ItemList)-1) |
SET @ItemList=STUFF(@ItemList,1,CHARINDEX(@delimiter,@ItemList),'') |
END |
INSERT @IDTable SELECT @ItemList |
RETURN |
END |
GO |
SELECT * FROM dbo.SPLIT('aaaa,bbb,ccc,eee',',') |
GO |
/* |
功能:实现split功能的函数 |
*/ |
create function dbo.fn_split |
( |
@inputstr varchar(8000), |
@seprator varchar(10) |
) |
returns @temp table (a varchar(200)) |
as |
begin |
declare @i int |
set @inputstr = rtrim(ltrim(@inputstr)) |
set @i = charindex(@seprator, @inputstr) |
while @i >= 1 |
begin |
insert @temp values(left(@inputstr, @i - 1)) |
set @inputstr = substring(@inputstr, @i + 1, len(@inputstr) - @i) |
set @i = charindex(@seprator, @inputstr) |
end |
if @inputstr <> '\' |
insert @temp values(@inputstr) |
return |
end |
go |
--调用 |
declare @s varchar(1000) |
set @s='1,2,3,4,5,6,7,8,55' |
select * from dbo.fn_split(@s,',') |
drop function dbo.fn_split |