SQL Like中的逗号分隔符

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  

转自：http://www.cnblogs.com/jhxk/articles/1802633.html