【SQL Server】判断两个字符串序列是否有交集


	【SQL Server】判断两个字符串序列是否有交集
[数据库教程]

1、创建UDF函数

假定有两个均以,作为分隔符字符串序列@m@n。判断@m@n是否有交集,我们可以创建如下的函数来实现,返回0为无交集,返回1为有交集。

/****** Object:  UserDefinedFunction [dbo].[Func_IsInner]    Script Date: 2021/02/23 10:47:05 ******/
IF OBJECT_ID(‘Func_IsInner‘) IS NOT NULL DROP FUNCTION Func_IsInner;

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[Func_IsInner]
( @m AS nvarchar(500) ,		--字符串m
  @n AS nvarchar(500) 		--字符串n
)
RETURNS bit
AS
BEGIN
	-- 判断两个‘,‘分隔的字符串是否有交集
	DECLARE @ret BIT, @x nvarchar(510), @y nvarchar(510);
	SET @x = ‘,‘+@m+‘,‘;
	SET @y=‘,‘+@n+‘,‘;
	IF ISNULL(@m, ‘‘)=‘‘ OR ISNULL(@n, ‘‘)=‘‘ 
		SET @ret = 0;
	IF EXISTS(
		SELECT
			result = substring(@x,number,charindex(‘,‘,@x+‘,‘,number)-number)
		FROM master.dbo.spt_values
		WHERE TYPE=‘p‘ AND NUMBER>1 AND NUMBER<LEN(@x)
		AND SUBSTRING(‘,‘+@x,NUMBER,1)=‘,‘
		AND CHARINDEX(SUBSTRING(@x,NUMBER-1,CHARINDEX(‘,‘,@x+‘,‘,NUMBER)-NUMBER + 2),‘,‘+@y)>0)
		 SET @ret = 1;
	ELSE 
		SET @ret = 0;
	RETURN @ret;
END
GO

2、测试结果

根据几个测试case生成的SQL脚本:

SELECT ‘测试1 →‘, dbo.Func_IsInner(‘1,a‘, ‘1,s‘) AS ‘result‘;
SELECT ‘测试2 →‘, dbo.Func_IsInner(‘1,a‘, ‘s‘) AS ‘result‘;
SELECT ‘测试3 →‘, dbo.Func_IsInner(‘1,a‘, ‘m,n‘) AS ‘result‘;
SELECT ‘测试4 →‘, dbo.Func_IsInner(‘1,a‘, ‘‘) AS ‘result‘;
SELECT ‘测试5 →‘, dbo.Func_IsInner(‘1,a‘, NULL) AS ‘result‘;
SELECT ‘测试6 →‘, dbo.Func_IsInner(NULL, NULL) AS ‘result‘;

测试结果(output列等于0为无交集,1为有交集):

title output result
测试1 → 1 OK
测试2 → 0 OK
测试3 → 0 OK
测试4 → 0 OK
测试5 → 0 OK
测试6 → 0 OK

原文:https://www.cnblogs.com/itheo/p/14434995.html

作者:Theo·Chan
版权:本文版权归作者和博客园共有
转载:欢迎转载,但未经作者同意,必须保留此段声明;必须在文章中给出原文连接;否则必究法律责任

【SQL Server】判断两个字符串序列是否有交集

原文地址:https://www.cnblogs.com/itheo/p/14434995.html