SQL 分隔字符串批量模糊查询

//分隔字符串函数
CREATE FUNCTION func_split (@str nvarchar(MAX),@split VARCHAR(10)) 
returns @t TABLE (col VARCHAR(100)) AS BEGIN
    DECLARE
        @i INT DECLARE
        @s INT 
        SET @i = 1 
        SET @s = 1
    WHILE
            (@i > 0) BEGIN            
                SET @i = charindex( @split,@str,@s )
            IF
                (@i > 0) BEGIN
                    INSERT @t(col) VALUES(SUBSTRING(@str,@s,@i -@s)) 
                END ELSE BEGIN
                    INSERT @t(col) VALUES(SUBSTRING(@str,@s, len(@str) -@s + 1)) 
                END 
                    SET @s = @i + 1 
            END RETURN 
END

// 使用函数案例
declare @drawingNo varchar(3000)
set @drawingNo='BZW08-2201141284,BZW08-2202120759'
select * from  dbo.func_split(@drawingNo,',')

 

 

//批量模糊查询案例方法
SELECT WORK_ORDER_NO from Orders where EXISTS( select * from (select col from  dbo.func_split('2201141284,2202120759,5454',',')) a where WORK_ORDER_NO like '%' + a.col +'%' )