【无标题】

最新推荐文章于 2024-07-20 20:04:43 发布

打工的苦逼日子

最新推荐文章于 2024-07-20 20:04:43 发布

阅读量48

点赞数

文章标签：数据库 sql

本文链接：https://blog.csdn.net/qq_46003418/article/details/126762060

版权

USE [AMIBA_DW]
GO

/****** Object: UserDefinedFunction [dbo].[f_ttInStr] Script Date: 2022/9/8 11:54:21 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

–该函数把传递过来的字符串转换成IN 后面的列表，可以处理以分号，逗号以及空格分隔的字符串

分隔字符串

create FUNCTION [dbo].[f_ttInStr]

         (@SourceStr varchar(8000))--源字符串

RETURNS @table table(list varchar(50) )

BEGIN

select @sourcestr = replace(@sourcestr,‘;’,‘,’)

select @sourcestr = replace(@sourcestr,’ ‘,’,')

–declare @OutStr varchar(200)

if charindex(‘,’,@sourcestr)>0

begin                                          

  declare @i int                                          

  declare @n int                                          

  set @i=1                                          

  while charindex(',',@sourcestr,@i)>0                                          

    begin                                          

       set @n=charindex(',',@sourcestr,@i)                                          

       insert into @table values(substring(@sourcestr,@i, @n-@i) )                                          

       set @i=@n+1                                          

    end                                          

    insert into @table values(substring(@sourcestr,@i,len(@sourcestr)-@i+1))                                          

end  else insert into @table values(@sourcestr)

delete from @table where isnull(list,‘’) = ‘’

return

END