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

转载于:https://www.cnblogs.com/kiant71/archive/2010/06/12/1757416.html