在t-sql中,如何检查一个字符串是否包含两个或更多类似的字符?
我有列包含nvarchar的手机号码,可以是“5512111445”、“6612888445”或隐藏的号码,如“5512zzz44x”
我有一个用户输入的搜索模式,可以是“xx12yy4zx”,我想返回所有匹配的数字到这个模式,其中x,y,z代表任何数字,但如果重复它将代表相同的数字。例如,前面的模式应该返回上面列出的所有数字。
xx是类似的数字,如55或66。而xy是不同的数字,比如45或67。
怎么能做到?

最佳答案

您可以将掩码和数字中的每个字符透视到列中,然后在掩码上单独分组,后跟掩码+数字。在此方法中,5512111445和6612888445与掩码xx12yy4yz不匹配,因为掩码中的y未映射到唯一的数字。但是,手机号5512111415和6612888485与掩码xx12yyy4yz匹配,手机号5512zzz44x也匹配。

--declare @mobileNums varchar(10)='5512111445'; --no match because @mask y maps to different values
--declare @mobileNums varchar(10)='6612888445'; --no match because @mask y maps to different values
--declare @mobileNums varchar(10)='5512111415'; --no match because @mask x should not equal @mask z
--declare @mobileNums varchar(10)='6612888485'; --matches
--declare @mobileNums varchar(10)='8812888485'; --no match because @mask x should not equal @mask y
--declare @mobileNums varchar(10)='5512zzz44x'; --matches because z and x are both hidden and different
--declare @mask varchar(10)='xx12yyy4yz';

declare @mobileNums varchar(10)='3211zyy'; -- no match because @mask y <> @mask z, but @mobileNums y = y
declare @mask varchar(10)='3211yxz';

declare @t table(n char, m char);
declare @i int=1;

while @i<=LEN(@mobileNums) begin
    insert into @t values (SUBSTRING(@mobileNums,@i,1), SUBSTRING(@mask,@i,1));
    set @i+=1;
end

if exists(
    -----------------------------------------------------------------------------
    -- Group by m
    select
    m, c=count(m)
    from @t
    where ISNUMERIC(n)=0 and ISNUMERIC(m)=0
    group by m
    except
    select
    m, c=count(m+n)
    from @t
    where ISNUMERIC(n)=0 and ISNUMERIC(m)=0
    group by m,n

    union

    select
    m, c=count(m)
    from @t
    where ISNUMERIC(n)=0 and ISNUMERIC(m)=1
    group by m
    except
    select
    m, c=count(m+n)
    from @t
    where ISNUMERIC(n)=0 and ISNUMERIC(m)=1
    group by m,n

    union

    select
    m, c=count(m)
    from @t
    where ISNUMERIC(n)=1 and ISNUMERIC(m)=0
    group by m
    except
    select
    m, c=count(m+n)
    from @t
    where ISNUMERIC(n)=1 and ISNUMERIC(m)=0
    group by m,n

    union

    select
    m, c=count(m)
    from @t
    where ISNUMERIC(n)=1 and ISNUMERIC(m)=1
    group by m
    except
    select
    m, c=count(m+n)
    from @t
    where ISNUMERIC(n)=1 and ISNUMERIC(m)=1
    group by m,n

    union

    -----------------------------------------------------------------------------
    -- Group by n

    -- Add a rule that no numeric @mobileNums digit can correspond to more than one alpha @mask character
    select
    n, c=count(m)
    from @t
    where ISNUMERIC(n)=1 and ISNUMERIC(m)=0
    group by n
    except
    select
    n, c=count(m+n)
    from @t
    where ISNUMERIC(n)=1 and ISNUMERIC(m)=0
    group by m,n

    union

    -- For GROUP BY n, include the three remaining combinations of ISNUMERIC(n) and ISNUMERIC(m)
    select
    n, c=count(m)
    from @t
    where ISNUMERIC(n)=0 and ISNUMERIC(m)=0
    group by n
    except
    select
    n, c=count(m+n)
    from @t
    where ISNUMERIC(n)=0 and ISNUMERIC(m)=0
    group by m,n

    union

    select
    n, c=count(m)
    from @t
    where ISNUMERIC(n)=0 and ISNUMERIC(m)=1
    group by n
    except
    select
    n, c=count(m+n)
    from @t
    where ISNUMERIC(n)=0 and ISNUMERIC(m)=1
    group by m,n

)
select patMatch='False'
else
select patMatch='True';

编辑-添加一条规则,即任何数字@mobilenums数字都不能对应于多个alpha@mask字符
编辑-对于GROUP BY n,包括isnumeric(n)和isnumeric(m)的其余三个组合
编辑-删除第八个UNION

09-09 20:32
查看更多