SQL Like中的逗号分隔符

  在与数据库交互的过程中，我们经常需要把一串ID组成的字符串当作参数传给存储过程获取数据。很多时候我们希望把这个字符串转成集合以方便用于in操作。 有两种方式可以方便地把这个以某种符号分隔的ID字符串转成临时表。

 

方式一：通过charindex和substring。 

create function func_splitstring
(@str nvarchar(max),@split varchar(10))
returns @t Table (c1 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(c1) values(substring(@str,@s,@i-@s))
        end   
        else begin
            insert @t(c1) values(substring(@str,@s,len(@str)-@s+1))
        end
        set @s = @i + 1   
    end
    return
end

执行：select * from  dbo.func_splitstring('1,2,3,4,5,6', ',')

结果：

 

 

方式二：通过XQuery（需要SQL Server 2005以上版本）。

create function func_splitid
(@str varchar(max),@split varchar(10))
RETURNS @t Table (c1 int)
AS
BEGIN
    DECLARE @x XML
    SET @x = CONVERT(XML,'<items><item id="' + REPLACE(@str, @split, '"/><item id="') + '"/></items>')
    INSERT INTO @t SELECT x.item.value('@id[1]', 'INT') FROM @x.nodes('//items/item') AS x(item)
    RETURN 
END

执行：select * from  dbo.func_splitid('1,2,3,4,5,6', ',')

结果：

 

其他写法:

 

alter FUNCTION [dbo].[func_split](@str nvarchar(4000),@separtor varchar(10))     

   returns @temp table([row] [int] IDENTITY(1,1) NOT NULL,value nvarchar(4000))     

   as      

 begin    

    declare @i int    

    set  @str=rtrim(ltrim(@str))     

    set  @i=charindex(@separtor,@str)     

   while   @i>=1     

     begin    

      insert   @temp   values(left(@str,@i-1))     

      set  @str=substring(@str,@i+1,len(@str)-@i)     

      set  @i=charindex(@separtor,@str)     

     end    

     if @str<>''      

     insert @temp values(@str)     

     return      

 end 

 

 

 

以上这个主要是用于类似
select * from t1 where id in
(
select id from dbo.func_splitid('1,2,3,4,5,6', ',')
)

 

 

 

 

假设我们有一字段名为name,其值是用逗号分隔的。

值为：'111,111xu2,1112'。

现在，我们需要编写语句搜索该name值 like '11'的。

按理说，这个name中没有11，我们要的结果就是返回空。

但是如果我们 select * from student where name like '%11%'的话，依然可以正常的查询出结果。

－－－

此时，我们应该采用如下的语句来实现：

 

select * from student where name like '%11%' --按照我的想法是不能查到的。但结果是查到了
--解决办法是：将sql字段名前后加上,号，并且比较值前后也加上。
--特别注意的是：字段名加逗号时，要用字符串连接的形式，不能直接 ',name,'
select * from student where ','+name+',' like '%,111,%'

 

 

 

sql中的某个字段用“，”分隔数据，
需要获取数据的时候直接把“，”拆分成数据，获得一个数据的list。

例如：需要查询某字段是否包含一个值，
111是否存在于1111,2111,1112,1121,1113这个字段中 。
因为根据“，”逗号分开，要求的答案是：不在字段中。

用传统的like '%111%',显然不合适，这样虽然111不存在但是依然能查到该条记录。
所以应该用以下语句实现：
select * from Table where ','+columA+',' like '%,111,%'。
实际就是把字段填上一个逗号然后在比较。如果你的字段是用别的分隔符，同理可得。

 

 in中数字少的话还可以用:

用“union all”替换“,”
如传入的“1,2,3,4,5”
替换为SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3...