Apply a Mask to Format a String in SQL Server Quer

问题:

Is there a neat way to apply a mask to a string in a SQL Server query?

I have two tables, one with Phone number stored as varchar with no literals 0155567890 and a phone type, which has a mask for that phone number type: (##) #### ####

What is the best way to return a string (for a merge Document) so that the query returns the fully formatted phone number:

[phone]

回答1:

I needed this also, and thanks to Sjuul's pseudocode, I was able to create a function to do this.

CREATE FUNCTION [dbo].[fx_FormatUsingMask] 
(
    -- Add the parameters for the function here
    @input nvarchar(1000),
    @mask nvarchar(1000)
)
RETURNS nvarchar(1000)
AS
BEGIN
    -- Declare the return variable here
    DECLARE @result nvarchar(1000) = ''
    DECLARE @inputPos int = 1
    DECLARE @maskPos int = 1
    DECLARE @maskSign char(1) = ''

    WHILE @maskPos <= Len(@mask)
    BEGIN
        set @maskSign = substring(@mask, @maskPos, 1)

        IF @maskSign = '#'
        BEGIN
            set @result = @result + substring(@input, @inputPos, 1)
            set @inputPos += 1
            set @maskPos += 1
        END
        ELSE
        BEGIN
            set @result = @result + @maskSign
            set @maskPos += 1
        END
    END
    -- Return the result of the function
    RETURN @result

END

回答2:

This is just what came up in my head. I don't know whether it's the best solution but I think it should be workable.

Make a function with the name applyMask (orso)

Pseudocode:

WHILE currentPosition < Length(PhoneNr) AND safetyCounter < Length(Mask)
    IF currentSign = "#"
        result += Mid(PhoneNr, currentPosition, 1)
        currentPosition++
    ELSE
        result += currentSign
        safetyCounter++
    END
END
Return result

回答3:

Just in case if someone will ever need table valued function.

Approach 1

create function ftMaskPhone
(
    @phone varchar(30),
    @mask varchar(50)
)
returns table as
return
    with ci(n, c, nn) as (
        select
            1,
            case
                when substring(@mask, 1, 1) = '#' then substring(@phone, 1, 1)
                else substring(@mask, 1, 1)
            end,
            case when substring(@mask, 1, 1) = '#' then 1 else 0 end
        union all
        select
            n + 1,
            case
                when substring(@mask, n + 1, 1) = '#' then substring(@phone, nn + 1, 1)
                else substring(@mask, n + 1, 1)
            end,
            case when substring(@mask, n + 1, 1) = '#' then nn + 1 else nn end
        from ci where n < len(@mask))
    select (select c + '' from ci for xml path(''), type).value('text()[1]', 'varchar(50)') PhoneMasked
GO

Then apply it as

declare @mask varchar(50)
set @mask = '(##) #### ####'

select pm.PhoneMasked
from Phones p
    outer apply ftMaskPhone(p.PhoneNum, @mask) pm

---

Approach 2

Let's leave abowe one for history. Below one is more performant.

create function ftMaskPhone
(
    @phone varchar(30),
    @mask varchar(50)
)
returns table as
return
    with v1(N) as (
        select 1 union all select 1 union all select 1 union all select 1 union all select 1
        union all
        select 1 union all select 1 union all select 1 union all select 1 union all select 1
    ),
    v2(N) as (select 1 from v1 a, v1 b),
    v3(N) as (select top (isnull(len(@mask), 0)) row_number() over (order by @@spid) from v2),
    v4(N, C) as (
        select N, isnull(substring(@phone, case when c.m = 1 then row_number() over (partition by c.m order by N) end, 1), substring(@mask, v3.N, 1))
        from v3
            cross apply (select case when substring(@mask, v3.N, 1) = '#' then 1 end m) c
    )
    select Value = (
        select c + ''
        from v4
        order by N
        for xml path(''), type
    ).value('text()[1]', 'varchar(50)')
go

回答4:

If you need to "mask", rather hide the real value with another, and then "unmask" a string you can try this function, or extend it for that matter. :)

https://stackoverflow.com/a/22023329/2175524