Version 1: Compare by Separator
Algorithm concept: truncate the search string loop by separator and compare it with the characters to be searched
Copy codeThe 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 searched
Copy 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