目标:select * from[dbo].[splitstr]('01,02,03',',')
返回结果 表
01
02
03
自定义函数 CREATE function [dbo].[splitstr](@SourceSql varchar(8000),@StrSeprate varchar(5))
returns @temp table(TID varchar(2000))
as
begin
declare @i int
set @SourceSql=rtrim(ltrim(@SourceSql))
set @i=charindex(@StrSeprate,@SourceSql)
while @i>=1
begin
insert @temp values(left(@SourceSql,@i-1))
set @SourceSql=substring(@SourceSql,@i+1,len(@SourceSql)-@i)
set @i=charindex(@StrSeprate,@SourceSql)
end
if @SourceSql<>'' insert @temp values(@SourceSql)
——————————————————————————————————————————————————————————————
SQL实现split函数,自定义分割字符,自定义取出第几个分割字符前的字符串
自定义取出第几个分割字符前的字符串,默认位置(0)
格式:dbo.split(字段名,'分隔字符',取出的第几个字符串)
如果没有分隔的字符,则返回整个字符串。
如果取出的位置字符串的位置超出Index则返回空。
CREATE FUNCTION [dbo].[split]
(@str nvarchar(4000),@code varchar(10),@no int )
RETURNS varchar(200)
AS
BEGIN
declare @intLen int
declare @count int
declare @indexb int
declare @indexe int
set @intLen=len(@code)
set @count=0
set @indexb=1
if @no=0
if charindex(@code,@str,@indexb)<>0
return left(@str,charindex(@code,@str,@indexb)-1)
else
return @str
while charindex(@code,@str,@indexb)<>0
begin
set @count=@count+1
if @count=@no
break
set @indexb=@intLen+charindex(@code,@str,@indexb)
end
if @count=@no
begin
set @indexe=@intLen+charindex(@code,@str,@indexb)
if charindex(@code,@str,@indexe)<>0
return substring(@str,charindex(@code,@str,@indexb)+len(@code),charindex(@code,@str,@indexe)-charindex(@code,@str,@indexb)-len(@code))
else
return right(@str,len(@str)-charindex(@code,@str,@indexb)-len(@code)+1)
end
return ''
END
return
end
————————————————————————————————————————————————————————————————
将IDS分割后并转化为对应的字符
- --将档案IDS的字符串转换成名字的字符串
- ALTER FUNCTION GetArchivesNameStr(@SourceSql nvarchar(500),@StrSeprate nvarchar(10)) --字符串格式41,56,77,
- RETURNS nvarchar(500)
- AS
- begin
- declare @names nvarchar(500)
- declare @i int
- set @names=''
- set @SourceSql=rtrim(ltrim(@SourceSql))
- set @i=charindex(@StrSeprate,@SourceSql)
- while @i>=1
- begin
- set @names=@names+(select name from Archives where ArchivesID=convert(int,left(@SourceSql,@i-1)))+','
- set @SourceSql=substring(@SourceSql,@i+1,len(@SourceSql)-@i) --已经转换好的去掉
- set @i=charindex(@StrSeprate,@SourceSql) --重新定位
- end
- --return @names
- if @SourceSql<>''and @i>=1 --最后一个
- begin
- set @names=@names+(select name from Archives where ArchivesID=convert(int,left(@SourceSql,@i-1)))
- end
- if(right(@names,1)=',') --去掉后面的逗号
- set @names=substring(@names,1,len(@names)-1)
- return @names
- end