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

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