version One: comparing by separator
Algorithm idea: Intercept the search string loop by separator and compare the characters to be searched
Copy Code code as follows:
Use [Fly]
Go
--Parameters: @inStr to search strings, @fndStr search strings, @doc separator
For example: Select Dbo.fsearch (' 1,2,3,4,5,6 ', ' 3,6,5,8,2 ', ', ') return 0-mismatch, return 1-match (with @fndstr content in @instr)
--can be used for table searches, such as:
--Select *,dbo.fsearch (str, ' 3,6,5,8,2 ', ', ') as to match the from table name
--@fndStr and @doc two parameters need to be provided by yourself, @inStr can be the name of a field to be searched in the datasheet
CREATE FUNCTION Cgf_fn_search (@inStr VARCHAR, @fndStr VARCHAR (), @doc VARCHAR (5))
RETURNS INT
As
BEGIN
DECLARE @i int,@c VARCHAR (+), @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 Two: verbatim comparisons
Algorithm thinking: Verbatim interception search string loops compared to the characters to be searched
Copy Code code 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, @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
