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 table] ') and OBJECTPROPERTY (id, n' isusertable') = 1)
Drop table [ordinal table]
GO
-- For efficiency, a secondary table must be used together.
Select top 1000 id = identity (int,) into sequence table
From syscolumns a, syscolumns B
Alter table ordinal number table add constraint pk_id _ ordinal number table primary key (id)
Go
/* -- Generate a fuzzy match String Based on the specified string
The condition connection keyword is and, or.
Brackets can be specified arbitrarily.
The generated conditional expression is like fuzzy match.
-- Producer build 2004.08 (reference please keep this information )--*/
/* -- Call example
-- Call example
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 ')
--*/
-- Example Function
Create function f_ SQL (
@ Str Nvarchar (1000), -- string to be retrieved
@ Fdname sysname -- search for the Field
) 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 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
Go