String processing (Fuzzy unordered search)

-----------------------------------

-- Author: htl258 (Tony)

-- Title: string processing (Fuzzy unordered search)

-- Date: 21:26:00

Http://blog.csdn.net/htl258

-- Keep this information for reference.

-----------------------------------

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', 'Go deep into sqlserver2005 Development Management and Application instance' Union all

Select '002 ', 'sqlserver2005 Performance Tuning' Union all

Select '003 ',' SQL full Manual' Union all

Select '004 ', 'sqlserver2005 technology insider storage engine' Union all

Select '005 ',' SQL book 'Union all

Select '006 ', 'sqlserver2000 advanced management and developing' Union all

Select '007 ', 'sqlserver2005 database development details' Union all

Select '008 ', 'sqlserver2000 stored procedure and XML programming' Union all

Select '009', 'sqlserver2005 System Management records' Union all

Select '010 ',' SQL Technical Manual'

Go

 

-- Create a UDF

 

If not object_id ('fn _ str') is null

Drop function fn_str

Go

Create Function fn_str (

@ STR varchar (100), -- input string
@ Flag varchar (10) -- delimiter
)

Returns @ t table (A varchar (10 ))

As

Begin

Set @ STR = rtrim (ltrim (@ Str ))

While Len (@ Str)> 0

Begin

Insert @ t select left (@ STR, charindex (@ flag, @ STR + @ flag)-1)

Set @ STR = ltrim (stuff (ltrim (@ Str), 1,

Charindex (@ flag, ltrim (@ Str) + @ flag ),''))

End

Return

End

Go

 

 

-- 1. Full match of all keywords

Declare @ s varchar (100)

Set @ s = 'SQL 2000'

Select * From ta t

Where not exists (

Select 1

From fn_str (@ s ,'')

Where charindex (A, T. Title) = 0)

/*

Id title

-------------------------------------------

006 sqlserver2000 advanced management and development

 

(One row is affected)

*/

 

 

-- 2. Partial keyword matching

Set @ s = 'development 100'

Select * From ta t

Where exists (

Select 1

From fn_str (@ s ,'')

Where charindex (A, T. Title)> 0)

/*

Id title

-------------------------------------------

001 in-depth introduction to sqlserver2005 Development Management and Application Instances

006 sqlserver2000 advanced management and development

007 sqlserver2005 database development details

008 sqlserver2000 stored procedure and XML Programming

 

(Four rows affected)

*/

This article from the csdn blog, reproduced please indicate the source: http://blog.csdn.net/htl258/archive/2009/09/07/4525799.aspx