1,获取字符串中分隔符分隔的长度
alter FUNCTION GetLenBySelChar
(
-- Add the parameters for the function here
@StrParams nvarchar(4000),
@char char(1)=N'1'
)
RETURNS int
AS
BEGIN
declare @m int,
@length int
set @length=0
set @m=CHARINDEX(@char,@strparams)
WHILE @m>0
BEGIN
set @strparams=substring(@strparams,@m+1,datalength(@strparams)-1)
set @length=@length+1
set @m=CHARINDEX(@char,@strparams)
END
return @length
END
2,返回每个分隔的字符串
ALTER FUNCTION [dbo].[funGetSubStrBySelChar]
(
-- Add the parameters for the function here
@StrParams nvarchar(4000),
@char char(1)=N'1'
)
RETURNS nvarchar(4000)
AS
BEGIN
declare --@str varchar(300),
@id varchar(3000),
@m int,
@n int
--set @str='a,b,c,d,e'
--set @m=CHARINDEX(',',@str)
--set @n=1
--WHILE @m>0
--BEGIN
-- set @id=substring(@str,@n,@m-@n)
-- print @id
-- set @n=@m+1
-- set @m=CHARINDEX(',',@str,@n)
--END
set @m=charindex(@char,@strparams)
set @n=1
if @m>0
begin
set @id=substring(@strparams,1,@m-@n)
end
else
begin
set @id=null
end
return @id
END
三,返回取出分隔符后新的字符串
ALTER FUNCTION [dbo].[funGetNewStr]
(
-- Add the parameters for the function here
@StrParams nvarchar(4000),
@char char(1)=N','
)
RETURNS nvarchar(4000)
AS
BEGIN
declare --@str varchar(300),
@id varchar(3000),
@m int,
@n int
--set @str='a,b,c,d,e'
--set @m=CHARINDEX(',',@str)
--set @n=1
--WHILE @m>0
--BEGIN
-- set @id=substring(@str,@n,@m-@n)
-- print @id
-- set @n=@m+1
-- set @m=CHARINDEX(',',@str,@n)
--END
set @m=charindex(@char,@strparams)
if @m>0
begin
set @strparams=substring(@strparams,@m+1,datalength(@strparams)-@m-1)
end
return @strparams
END