1.方案1如下: ---例如---------------------- 2.方案使用xml方式 ---例如------------------------ =============================================
-- 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,',',')
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,',',')