从目标字符串中查找是否有指定一个或多个子字符串-优快云博客

博客展示了一个SQL函数fn_FindString的创建过程，该函数用于从目标字符串中查找指定的一个或多个子字符串。函数先将目标字符串和需匹配字符串拆分为子字符串，再通过JOIN操作找出所有匹配项，最后给出了函数调用示例。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

/**************************************
*fn_FindString
*
*从目标字符串中查找是否有指定一个或多个子字符串
*
*xspf.cn@gmail.com
***************************************/

--@StrDest:目标字符串','分隔
--@StrPattern:需匹配字符串(','分隔)

--返回：找到的所有匹配项

IF EXISTS(SELECT [name] FROM sysobjects WHERE [name] = 'fn_FindString' AND [type] = 'tf')
BEGIN
DROP FUNCTION fn_FindString
END

CREATE FUNCTION fn_FindString(@StrDest varchar(255),@StrPattern varchar(255))
RETURNS @Result table(substr varchar(31))
AS
BEGIN
DECLARE @StartPos int,
@CommaPos int

DECLARE @DestSubstrTable table(substr varchar(31))
DECLARE @PatternSubstrTable table(substr varchar(31))

/*需匹配的字符串拆分为子字符串*/
SET @StartPos = 0
SET @CommaPos = CHARINDEX(',',@StrPattern,@StartPos)

WHILE(@CommaPos!=0)
BEGIN

INSERT INTO @PatternSubstrTable VALUES(LTRIM(RTRIM(SUBSTRING(@StrPattern,@StartPos,@CommaPos-@StartPos))))

SET @StartPos = @CommaPos + 1
SET @CommaPos = CHARINDEX(',',@StrPattern,@StartPos)
END

INSERT INTO @PatternSubstrTable VALUES(LTRIM(RTRIM(SUBSTRING(@StrPattern,@StartPos,LEN(@StrPattern)+1-@StartPos))))

/*目标字符串拆分为子字符串*/
SET @StartPos = 0
SET @CommaPos = CHARINDEX(',',@StrDest,@StartPos)

WHILE(@CommaPos!=0)
BEGIN

INSERT INTO @DestSubstrTable VALUES(LTRIM(RTRIM(SUBSTRING(@StrDest,@StartPos,@CommaPos-@StartPos))))

SET @StartPos = @CommaPos + 1
SET @CommaPos = CHARINDEX(',',@StrDest,@StartPos)
END

INSERT INTO @DestSubstrTable VALUES(LTRIM(RTRIM(SUBSTRING(@StrDest,@StartPos,LEN(@StrDest)+1-@StartPos))))

/*结果为目标字符串中所有匹配项*/
INSERT INTO @Result
SELECT lhs.* FROM @DestSubstrTable AS lhs JOIN @PatternSubstrTable AS rhs
ON lhs.subStr = rhs.subStr

RETURN

END

select * from fn_FindString('10,20,25,26,28,30,32','12,1,20,30')