Version 1: Compare by Separator
Algorithm Train of Thought: truncate the search string loop by separator and compare it with the characters to be searched
CopyCode The Code is as follows: use [Fly]
Go
-- Parameter: @ instr string to be searched, @ fndstr string to be searched, @ Doc Separator
-- Example: Select DBO. fsearch ('1, 2, 3, 4, 5, 6', '3, 6, 5, 8, 2 ',', ') returns 0-unmatched, return 1-match (@ instr contains @ fndstr)
-- Can Be Used for table search, such:
-- Select *, DBO. fsearch (STR, '3, 6, 5, 8, 2 ',', ') whether the as matches the from Table Name
-- @ Fndstr and @ Doc parameters must be provided by you. @ instr can be the name of a field to be searched in the data table.
Create Function cgf_fn_search (@ instr varchar (500), @ fndstr varchar (500), @ Doc varchar (5 ))
Returns int
As
Begin
Declare @ I int, @ C varchar (500), @ fstr varchar (500)
Set @ fstr = @ fndstr
While (LEN (@ fstr)> 0)
Begin
Set @ I = charindex (@ doc, @ fstr)
If (@ I = 0)
Begin
If (charindex (@ fstr, @ instr)> 0)
Return 1
Else
Return 0
End
Else
Begin
Set @ C = substring (@ fstr, 1, @ i-1)
If (charindex (@ C, @ instr)> 0)
Return 1
Else
Set @ fstr = substring (@ fstr, @ I + Len (@ DOC), Len (@ fstr ))
End
End
Return 0
End
Version 2: Comparison by words
Algorithm concept: extract the search string loop by word and compare the characters to be searchedCopy codeThe Code is as follows: use [Fly]
Go
/***** Object: userdefinedfunction [DBO]. [cgf_fn_searchchar] script Date: 09/03/2010 16:42:12 ******/
Set ansi_nulls on
Go
Set quoted_identifier on
Go
Create Function [DBO]. [cgf_fn_searchchar] (@ instr varchar (500), @ fndstr varchar (500 ))
Returns int
As
Begin
Declare @ I int, @ F int, @ C varchar (1)
Set @ I = 1
Set @ F = Len (@ fndstr)
While (@ I <= @ F)
Begin
Set @ C = substring (@ fndstr, @ I, @ I)
If (charindex (@ C, @ instr)> 0)
Begin
Return 1
End
Set @ I = @ I + 1
End
Return 0
End