ms sql server 字符串分隔（sqlserver字符串切割）

1.方案1如下：

-- =============================================
-- Description: split函数 -- Debug:select * from dbo.Fun_Split('ABC:BC:C:D:E',':')
-- 修改時間: 
-- 修改人:
-- =============================================
create FUNCTION [dbo].[F_StringSplit] 
(
 @SourceSql varchar(8000), 
 @StrSeprate varchar(10)
)
RETURNS 
@TEMP_Table TABLE ([text] varchar(2000)) -- collate Chinese_Taiwan_Stroke_CS_AS) --Chinese_Taiwan_Stroke_CI_AS 不區分大小寫
AS
BEGIN
 DECLARE @i int
 SET @SourceSql=rtrim(ltrim(@SourceSql))
 if RIGHT(@SourceSql,LEN(@StrSeprate))<>@StrSeprate --追加代码
 set @SourceSql=@SourceSql+@StrSeprate --追加代码
 SET @i=charindex(@StrSeprate,@SourceSql)
 WHILE @i>=1
 BEGIN
 if rtrim(ltrim(left(@SourceSql,@i-1)))='' --追加代码start
 begin
 SET @SourceSql=substring(@SourceSql,@i+1,len(@SourceSql)-@i)
 SET @i=charindex(@StrSeprate,@SourceSql)
 continue
 end--追加代码end
 INSERT @TEMP_Table VALUES(left(@SourceSql,@i-1))
 SET @SourceSql=substring(@SourceSql,@i+1,len(@SourceSql)-@i)
 SET @i=charindex(@StrSeprate,@SourceSql)
 END
 RETURN 
END

---例如----------------------

select * from F_StringSplit(',45,89,66,78,59,,41,36,',',')

2.方案使用xml方式

create function F_StringSplit
(
 @source varchar(8000),
 @splitStr varchar(20)
)
returns @tmp table(result xml)
as
begin
 set @source=LTRIM(RTRIM(isnull(@source,'')))
 set @splitStr=LTRIM(RTRIM(isnull(@splitStr,'')))
 declare @xml xml,@delStr varchar(6)
 set @delStr=''
 if @source<>'' and @splitStr<>''
 begin
 set @xml=CONVERT(xml,'<v>'+REPLACE(@source,@splitStr,'</v><v>')+'</v>')
 set @xml.modify('delete /v[.=sql:variable("@delStr")]')
 insert into @tmp select t.v.query('.') from @xml.nodes('/v/text()') t(v)
 end
 return
end

---例如----------------------

select * from F_StringSplit(',45,89,66,78,59,,41,36,',',')