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 |
