22,300
社区成员




/*
名称:fn_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 @str VARCHAR(100)
SET @str='ABC*BCD+A-MPP'
SET @str= replace(replace(REPLACE(@str,'*',','),'+',','),'-',',')
select * from
dbo.fn_split(@str,',')
a
------
ABC
BCD
A
MPP
(4 row(s) affected)
select replace(replace(replace('ABC*BCD+A-MPP','-',''),'*',''),'+','')
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ABCBCDAMPP
(1 行受影响)
select replace(replace(replace('ABC*BCD+A-MPP','-',''),'*',''),'+','')
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ABCBCDAMPP
(1 行受影响)