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='a12a34a56a' select * from dbo.fn_split(@s,'a') drop function dbo.fn_split /*a -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 12 34 56*/
create function uf_charseparate(@string varchar(8000),@s_char varchar(20)) returns @t table(id int identity(1,1),s_char varchar(20)) as begin declare @p int declare @char varchar(20) set @string=@string+@s_charset @p=charindex(@s_char,@string) while(@p>0) begin set @char=left(@string,@p-1) if (len(@char)>0) begin insert @t(s_char) values(@char) end set @string=right(@string,len(@string)-@p-len(@s_char)+1) set @p=charindex(@s_char,@string) end return end go select * from dbo.uf_charseparate('a12a34a56a','a') 1 12 2 34 3 56
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='a12a34a56a'
select * from dbo.fn_split(@s,'a')
drop function dbo.fn_split
/*a
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 12
34
56*/
returns @t table(id int identity(1,1),s_char varchar(20))
as
begin
declare @p int
declare @char varchar(20)
set @string=@string+@s_charset @p=charindex(@s_char,@string)
while(@p>0)
begin
set @char=left(@string,@p-1)
if (len(@char)>0)
begin
insert @t(s_char) values(@char)
end
set @string=right(@string,len(@string)-@p-len(@s_char)+1)
set @p=charindex(@s_char,@string)
end
return
end
go
select * from dbo.uf_charseparate('a12a34a56a','a')
1 12
2 34
3 56