1、创建UDF
函数
假定有两个均以,
作为分隔符字符串序列@m
和@n
。判断@m
和@n
是否有交集,我们可以创建如下的函数来实现,返回0
为无交集,返回1
为有交集。
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 NUMBER0)
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 |
作者:Theo·Chan原文:https://www.cnblogs.com/itheo/p/14434995.html
版权:本文版权归作者和博客园共有
转载:欢迎转载,但未经作者同意,必须保留此段声明;必须在文章中给出原文连接;否则必究法律责任
【SQL Server】判断两个字符串序列是否有交集
原文地址:https://www.cnblogs.com/itheo/p/14434995.html