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

Z时代
2024-01-10
分类：综合

database

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 →

测试2 →

测试3 →

测试4 →

测试5 →

测试6 →