字符串处理(模糊无序搜索)

-----------------------------------
-- Author: htl258(Tony)
-- Title : 字符串处理(模糊无序搜索)
-- Date : 2009-09-06 21:26:00

-- 引用请保留此信息.
-----------------------------------

IF NOT OBJECT_ID('TA') IS NULL
DROP TABLE TA
GO
CREATE TABLE TA (ID VARCHAR(3),Title VARCHAR(39))
INSERT INTO TA
SELECT '001','深入浅出SQLServer2005开发管理与应用实例' UNION ALL
SELECT '002','SQLServer2005性能调校' UNION ALL
SELECT '003','SQL完全手册' UNION ALL
SELECT '004','SQLServer2005技术内幕存储引擎' UNION ALL
SELECT '005','SQL宝典' UNION ALL
SELECT '006','SQLServer2000高级管理与开发' UNION ALL
SELECT '007','SQLServer2005数据库开发详解' UNION ALL
SELECT '008','SQLServer2000存储过程与XML编程' UNION ALL
SELECT '009','SQLServer2005系统管理实录' UNION ALL
SELECT '010','SQL技术手册'
GO

--创建自定义函数
IF NOT OBJECT_ID('FN_STR') IS NULL
DROP FUNCTION FN_STR
GO
CREATE FUNCTION FN_STR(@STR VARCHAR(100))
RETURNS @T TABLE(A VARCHAR(10))
AS
BEGIN
WHILE LEN(@STR)>0
BEGIN
INSERT @T SELECT LEFT(@STR,1)
SET @STR=RIGHT(@STR,LEN(@STR)-1)
END
RETURN
END
GO

DECLARE @S VARCHAR(100)
SET @S='SQL手册'

SELECT *
FROM TA T
WHERE NOT EXISTS(
SELECT 1
FROM FN_STR(@S)
WHERE CHARINDEX(A,T.TITLE)=0)
/*
ID Title
---- ---------------------------------------
003 SQL完全手册
010 SQL技术手册

(2 行受影响)
*/
SET @S='SQL2005'
SELECT *
FROM TA T
WHERE NOT EXISTS(
SELECT 1
FROM FN_STR(@S)
WHERE CHARINDEX(A,T.TITLE)=0)
/*
ID Title
---- ---------------------------------------
001 深入浅出SQLServer2005开发管理与应用实例
002 SQLServer2005性能调校
004 SQLServer2005技术内幕存储引擎
007 SQLServer2005数据库开发详解
009 SQLServer2005系统管理实录

(5 行受影响)
*/