SQL Server compares whether a string contains one character in another string

SQL compares the implementation code of one character in another string. For more information, see.

SQL compares the implementation code of one character in another string. For more information, see.

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