/*
功能:实现split功能的函数
*/create function dbo.fn_split
(
@inputstr varchar(8000),
@seprator varchar(10)
)
returns @temp table (a varchar(200))
as begin
declare @i intset @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)
endif @inputstr <> '\'
insert @temp values(@inputstr)return
end
go--调用declare @str varchar(1000)set @str='a-1;b-2;c-3;'--a,b,c
select 'abc' = left(a , charindex('-',a) -1) from (select * from dbo.fn_split(@str,';')) t1 where a <> ''
/*
abc
-----
a
b
c(所影响的行数为 3 行)
*/--1,2,3
select '123' = substring(a , charindex('-',a) + 1 , len(a)) from (select * from dbo.fn_split(@str,';')) t1 where a <> ''
/*
123
-----
1
2
3(所影响的行数为 3 行)
*/drop function dbo.fn_split
功能:实现split功能的函数
*/create function dbo.fn_split
(
@inputstr varchar(8000),
@seprator varchar(10)
)
returns @temp table (a varchar(200))
as begin
declare @i intset @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)
endif @inputstr <> '\'
insert @temp values(@inputstr)return
end
go--调用declare @str varchar(1000)set @str='a-1;b-2;c-3;'--a,b,c
select 'abc' = left(a , charindex('-',a) -1) from (select * from dbo.fn_split(@str,';')) t1 where a <> ''
/*
abc
-----
a
b
c(所影响的行数为 3 行)
*/--1,2,3
select '123' = substring(a , charindex('-',a) + 1 , len(a)) from (select * from dbo.fn_split(@str,';')) t1 where a <> ''
/*
123
-----
1
2
3(所影响的行数为 3 行)
*/drop function dbo.fn_split
功能:实现split功能的函数
*/create function dbo.fn_split
(
@inputstr varchar(8000),
@seprator varchar(10)
)
returns @temp table (a varchar(200))
as begin
declare @i intset @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)
endif @inputstr <> '\'
insert @temp values(@inputstr)return
end
go--调用declare @str varchar(1000)set @str='a-1;b-2;c-3;'--a,b,c
select col_abc = left(a , charindex('-',a) -1) into tb1 from (select * from dbo.fn_split(@str,';')) t1 where a <> ''
declare @output1 varchar(8000)
select @output1 = coalesce(@output1 + ';' , '') + col_abc from tb1
print @output1+';'
/*
a;b;c;
*/--1,2,3
select col_123 = substring(a , charindex('-',a) + 1 , len(a)) into tb2 from (select * from dbo.fn_split(@str,';')) t1 where a <> ''
declare @output2 varchar(8000)
select @output2 = coalesce(@output2 + ';' , '') + col_123 from tb2
print @output2+';'
/*
1;2;3;
*/drop function dbo.fn_split
drop table tb1,tb2
declare @str as varchar(20)
set @str='a-1;b-2;c-3;'select 'abc' = left(col1,charindex('-',col1) -1) + ';' +
left(col2,charindex('-',col2) -1) + ';' +
left(col3,charindex('-',col3) -1) + ';',
'123' = substring(col1,charindex('-',col1) + 1 , len(col1)) + ';' +
substring(col2,charindex('-',col2) + 1 , len(col2)) + ';' +
substring(col3,charindex('-',col3) + 1 , len(col3)) + ';'
from
(
select
PARSENAME(replace(left(@str,len(@str)-1),';' , '.'),3) as col1,
PARSENAME(replace(left(@str,len(@str)-1),';' , '.'),2) as col2,
PARSENAME(replace(left(@str,len(@str)-1),';' , '.'),1) as col3
) t/*
abc 123
---------- ---------
a;b;c; 1;2;3;(所影响的行数为 1 行)
*/