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