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...