37. Generate fuzzy match string for query

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_Sql]') and xtype in (N'FN', N'IF', N'TF') drop function [dbo].[f_Sql] GO if exists (select * from dbo.sysobjects where id = object_id(N'[ordinal]') and OBJECTPROPERTY(id, Select top 1000 id=identity(int,1,1) into sysobjects from sysobjects where sysobjects = 1 and sysobjects = 1 and sysobjects = 1 A,syscolumns B ALTER table table add constraint pk_id_ primary key(id) go /* Select A= dbo.f_SQL ('(Web or HTML or Internet) and (Programmer or) Developer)','content') select B=dbo.f_Sql('Web or HTML or Internet','content') select C=dbo.f_Sql('(Web and HTML)','content') select D=dbo.f_Sql('Web','content') --*/ -- create function f_Sql(@str Nvarchar(1000), Returns Nvarchar(4000) as begin Declare @r Nvarchar(4000) set @r= "select @r=@r+case when substring(@str,id,charindex(' ',@str+' ',id)-id) in('or','and') then ' '+substring(@str,id,charindex(' ',@str+' ',id)-id)+' ' when substring(@str,id,1)='(' then '(['+@fdname+'] like ''%' +substring(@str,id+1,charindex(' '@ STR + "', id) - id - 1) + '%' ' 'when the substring (@ STR, charindex (' @ STR + "', id) - 1, 1) = '), 'then' [' + @ fdname +] like '%' +substring(@str,id,charindex(' ',@str+' ',id)-id-1) +'%'')' else '['+@fdname+'] like ''%' +substring(@str,id,charindex(' '@ STR + "', id) - id) + '%' end from ordinal table where id < = len (@ STR) and charindex (', '+ @ STR, id) - id = 0 return (@ r) end goCopy the code