sqlserver 自定义函数

USE [STour]
GO
/****** Object:  UserDefinedFunction [dbo].[f_split]    Script Date: 08/22/2013 09:05:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER   function   [dbo].[f_split](@c   varchar(2000),@split   varchar(2))   
  returns   @t   table(col   varchar(100))   
  as   
    begin   
    
      while(charindex(@split,@c)<>0)   
        begin   
          insert   @t(col)   values   (substring(@c,1,charindex(@split,@c)-1))   
          set   @c   =   stuff(@c,1,charindex(@split,@c),'')   
        end   
      insert   @t(col)   values   (@c)   
      return   
    end  

用法：

查询

delete LS_Room_Guest where guestid not in 
(select col from dbo.f_split('LD1,LD2,1,2',','))

其中dbo.f_split 函数返回结果为 ‘LD1’,'LD2','1','2'