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
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
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
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
As noted by @Sean, SQL Server 2012 and up supports the FORMAT function, which almost gives you what you need, with the following caveats:
It takes a number to format, rather than a VARCHAR. This could be worked around by using a CAST.
The mask as provided ((##) #### ####), coupled with a CAST would remove the leading zero, leaving you with (1) 5556 7890. You could update the mask to (0#) #### ####. Going on a limb that you're representing an Australian phone number, it seems that the leading 0 is always there anyways:
Within Australia, to access the "Number" of a landline telephone in an "Area" other than that in which the caller is located (including a caller using a "Mobile" 'phone), firstly it is necessary to dial the Australian "Trunk Access Code" of 0 plus the "Area" code, followed by the "Local" Number. Thus, the "Full National Number" (FNN) has ten digits: 0x xxxx xxxx.
But ultimately, I would argue that SQL Server is not the best place to handle representation/formatting of your data (as with dates, so with phone numbers). I would recommend doing this client-side using something like Google's libphonenumber. When a phone number is entered into the database, you could store the phone number itself and the country to which it belongs, which you could then use when displaying the phone number (or doing something like calling it or checking for validity).
There is the built in FORMAT function, which almost works. Unfortunately it takes an int as the first parameter, so it strips off the leading zero:
select format(0155567890 ,'(##) #### ####')
(1) 5556 7890
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. :)
I wanted to hide some information, so i used RIGHT function. It shows only first 4 chars from right side.
CONCAT('xxx-xx-', RIGHT('03466045896', 4))
Above code will show "xxx-xx-5896"
