Apply a Mask to Format a String in SQL Server Query/View

Question

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:

(01) 5556 7890

Answer 1

As noted in the comment, my original answer below will result in terrible performance if used in a large number of rows. i-one's answer is preferred if performance is a consideration.

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

Answer 2

Just in case someone ever needs a table-valued function.

Approach 1 (see #2 for a faster version)

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

I'm going to leave the above version for historical purposes. However, this one has better performance.

CREATE FUNCTION dbo.ftMaskPhone
(
    @phone varchar(30),
    @mask varchar(50)
)
RETURNS TABLE 
WITH SCHEMABINDING
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 MaskedValue = (
        SELECT c + ''
        FROM v4
        ORDER BY N
        FOR XML PATH(''), TYPE
    ).value('text()[1]', 'varchar(50)')
);
GO

Schema binding, in combination with this being a single-statement table-valued-function, makes this version eligible for inlining by the query optimizer. Implement the function using a CROSS APPLY as in the example above, or for single values, like this:

SELECT *
FROM dbo.ftMaskPhone('0012345678910', '### (###) ###-####')

Results look like:

MaskedValue
001 (234) 567-8910