SQL 字符串分割函数

 

SQL语句实现按关健字模糊查询,并按匹配度排序 
http://blog.csdn.net/fcuandy/archive/2007/10/17/1829492.aspx 
 

IF  EXISTS (SELECT * FROM sys.objects 
    WHERE object_id = OBJECT_ID(N'[dbo].[fn_SplitStringToROWS]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn_SplitStringToROWS]
GO

/*
SELECT * FROM [dbo].[fn_SplitStringToROWS] ('1,2,4', ',')
结果：
id          v
----------- ----------------------------------------------------------------------------------------------------
1           1
2           2
3           4

(3 行受影响)

*/

CREATE FUNCTION [dbo].[fn_SplitStringToROWS]
(
    @SourceSql varchar(8000)
    ,@StrSeprate varchar(10))
RETURNS @temp TABLE(id int identity(1,1),v varchar(100))

BEGIN
    DECLARE @i int
    SET @SourceSql=rtrim(ltrim(@SourceSql))
    SET @i=charindex(@StrSeprate,@SourceSql)
    WHILE @i>=1
    BEGIN
        INSERT @temp VALUES(left(@SourceSql,@i-1))
        SET @SourceSql=substring(@SourceSql,@i+1,len(@SourceSql)-@i)
        SET @i=charindex(@StrSeprate,@SourceSql)
    END
    IF @SourceSql<>'\'
       INSERT @temp VALUES(@SourceSql)
    RETURN 
END

---

IF  EXISTS (SELECT * FROM sys.objects 
    WHERE object_id = OBJECT_ID(N'[dbo].[fn_SplitStr3]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn_SplitStr3]
GO

CREATE Function [dbo].[fn_SplitStr3]
(
    @strInput   varchar(8000)
    ,@strSeprate    varchar(20)
)
RETURNS   @temp   table(F1   varchar(100))   
AS    
/******
    ex: SELECT * FROM [dbo].[fn_SplitStr3] ('1,2,4', ',')
结果：
F1
----------------------------------------------------------------------------------------------------
1
2
4

(3 行受影响)
*/

BEGIN   
  DECLARE @intCount int
  DECLARE @ch varchar(100)   
     
  WHILE( len(@strInput)>0 )   
    BEGIN 
        SET @intCount = charindex(@strSeprate, @strInput)
        IF( @intCount>0 )
            BEGIN
                SET @ch = left(@strInput, @intCount-1)
                SET @strInput = right(@strInput, len(@strInput)-@intCount)
            END
        ELSE
            BEGIN
                SET @ch = @strInput
                SET @strInput = ''
            END
            
        INSERT INTO @temp(F1) VALUES(@ch)
    END   
    
  RETURN   
END

fn_SplitStr3

---

IF  EXISTS (SELECT * FROM sys.objects 
    WHERE object_id = OBJECT_ID(N'[dbo].[fn_SplitStr2]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn_SplitStr2]
GO

CREATE Function [dbo].[fn_SplitStr2]
(
    @SourceSql   varchar(8000)
    ,@StrSeprate    varchar(20)
)
RETURNS   @temp   table(F1   varchar(100))   
AS    
/******
    ex: SELECT * FROM [dbo].[fn_SplitStr2] ('1,2,4', ',')
结果为:
F1
----------------------------------------------------------------------------------------------------
1
2
4
 
(3 行受影响) 
*/

BEGIN   
  DECLARE @ch varchar(100)   
  SET @SourceSql=@SourceSql+@StrSeprate  
     
  WHILE(@SourceSql<>'')   
    BEGIN 
      SET @ch=left(@SourceSql,charindex(@StrSeprate,@SourceSql,1)-1)   
      INSERT   @temp   VALUES(@ch)   
      SET @SourceSql=stuff(@SourceSql,1,charindex(@StrSeprate,@SourceSql,1),'')   
    END   
    
  RETURN   
END
 
 

fn_SplitStr2