Regular Expressions in SQL Server to validate fax numbers

Question

I'm trying to use RegEx to validate phone numbers saved in a SQL Server 2016 Table. In this table there are thousands of fax numbers which stored as different formats. Ex : 800-123-4567, 800/123-4567, 800#123/4567 etc. Now I'm wanting to use RegEx to validate the phone numbers also output them in a way without any special characters or spaces in between. Ex : 8001234567.

Here's what I have tried which does not seems to work for some reason. If anyone out there could correct me what I'm doing wrong here, I would really appreciate it.

DECLARE @expres  VARCHAR(50) = '%[/,-,\,#]%'
DECLARE @cmpfax as CHAR(100);
SELECT @cmpfax = cmp_fax FROM cicmpy WHERE LTRIM(RTRIM(cmp_code)) = '100373' AND cmp_fax IS NOT NULL;
SELECT REPLACE(@cmpfax, @expres, '#');

Here's my dbfiddle for the above code which I've tested.

SQL Server doesn't support Regex, just basic pattern matching. — Thom A, Oct 13 '22 at 15:52
For the first part (finding values which contain characters other than digits) does this answer your question? [Check if a variable contains any non-numeric digits in SQL Server](https://stackoverflow.com/questions/18188634/check-if-a-variable-contains-any-non-numeric-digits-in-sql-server) — Thom A, Oct 13 '22 at 15:54
For the latter (removing characters other than digits), you could use something like the function I have in [this answer](https://stackoverflow.com/a/71662896/2029983) — Thom A, Oct 13 '22 at 15:55
As you state (on the deleted answer) that `TRANSLATE` isn't a recognised built-in function, you should really tag what version you *are* using, as `TRANSLATE` is available in all *fully* supported versions of SQL Server; if you don't tag a specific version then the users here will assume you are using the *latest* version (or at least one that is fully supported). — Thom A, Oct 13 '22 at 16:12
@Larnu in your answer - replacing the characters (latter part) I'm getting this error "The function 'STRING_AGG' may not have a WITHIN GROUP clause.". I did not change anything just tried as it is. Any idea? — Glenn94, Oct 13 '22 at 16:20
@Glenn94 What version are you *really* using? SQL Server 2014 is no longer supported. The oldest version still in mainstream support is SQL Server 2017, the first version that introduced `STRING_AGG`. If you get `The function 'STRING_AGG' may not have a WITHIN GROUP clause` you're using SQL Server 2017 at least — Panagiotis Kanavos, Oct 13 '22 at 16:20
@PanagiotisKanavos my bad before I was connected to wrong instance. So the correct version I'm on is SQL Server 2016 (Version 13.0.1742.0). And the error now I get when running Larnu's query is "'STRING_AGG' is not a recognized built-in function name." — Glenn94, Oct 13 '22 at 16:29
Version 13.0.1742.0? That's Security update for SQL Server 2016 RTM GDR: August 8, 2017; *why* has your SQL instance not seen a single bug or security update in over **5 years**? — Thom A, Oct 13 '22 at 17:02
@Larnu for real or is this a `Tuesday` joke? 2022 hasn't come out yet ...... It's not a joke????? — Panagiotis Kanavos, Oct 13 '22 at 18:05
Yes I'm installing the 2022 RC1 version now...btw we have the extended support for 2016 version which ends on Jul 14, 2026 I believe. — Glenn94, Oct 13 '22 at 18:39
Yes, it's real, @PanagiotisKanavos . 2017 is now in Extended support: [SQL Server 2017 Lifecycle](https://learn.microsoft.com/en-us/lifecycle/products/sql-server-2017) — Thom A, Oct 14 '22 at 08:48
Also, 2016 SP1 came with a ***HUGE*** wealth of features for Standard edition and lower due to a multitude of bugs with features that were meant to be enterprise only; you ***really*** need to get your instance updated. — Thom A, Oct 14 '22 at 09:05

score 0 · Answer 1 · answered Oct 14 '22 at 09:10

This is a variation on the answer I linked earlier, however, as the OP is on 2016 RTM, they can't use STRING_AGG. This, therefore, uses the "old" FOR XML PATH (and STUFF) method to reaggregate the characters.

CREATE OR ALTER FUNCTION [fn].[PatternCharacterReplace_XML] (@String varchar(8000), @Pattern varchar(100), @ReplacementCharacter varchar(1)) 
RETURNS table
AS RETURN
    WITH N AS(
        SELECT N
        FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)),
    Tally AS(
        SELECT TOP(LEN(@String))
               ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS I
    FROM N N1, N N2, N N3, N N4)
    SELECT (SELECT CASE WHEN V.C LIKE @Pattern THEN @ReplacementCharacter ELSE V.C END
            FROM Tally T
                 CROSS APPLY (VALUES(SUBSTRING(@String,T.I,1)))V(C)
            ORDER BY T.I
            FOR XML PATH(''),TYPE).value('(./text())[1]','varchar(8000)') AS ReplacedString;

Then you get the values and remove the characters with something like this:

SELECT YT.Fax
       PCRX.ReplacedString AS NewFax
FROM dbo.YourTable YT
     CROSS APPLY fn.PatternCharacterReplace_XML(YT.Fax, '[^0-9]', '') PCRX
WHERE YT.Fax LIKE '%[^0-9]%';

Regular Expressions in SQL Server to validate fax numbers

1 Answers1