SQL Server query using regex?

Question

I have a varchar field in a SQL Server database that stores phone numbers in many different ways, but they are all phone number essentially.

Examples:

8181234564
(818) 123 4564
818 - 123 - 4567

I was hoping I can use regex to strip out all non-numeric characters and then perform a like or "=" on .. can I do that?

forgot to mention: I only have read access.

score 2 · Answer 1 · answered May 17 '11 at 20:28

If you only have read access you probably cant create functions either.

If you can create a function you could use some of the existing solutions. If not, this is ugly, but it'd work for your examples:

declare @string varchar(50)
set @string = '(818) 123 -  4564'

select replace(replace(replace(replace(@string,'(',''),' ',''),')',''),'-','')

score 1 · Answer 2 · edited May 23 '17 at 12:13

1

Here is a similar question that has your answer:

How to strip all non-alphabetic characters from string in SQL Server?

One of the answers shows how to strip everything but numbers out of a string. Basically you will create a UDF and use regex to clean out your non-numeric characters. Then you can do your comparison.

edited May 23 '17 at 12:13

Community

1
1

answered May 17 '11 at 19:42

IAmTimCorey

16,412
5
39
75

Mark Wilkins · Accepted Answer · 2011-05-18T12:43:23.500

If you know that the field contains a phone number in some kind of valid form, then the following really ugly usage of LIKE would match a specific number. To find 818-123-4567:

select * from thetable where phonenum like ('%8%1%8%1%2%3%4%5%6%7%')

This, of course, would match invalid entries as well (e.g., numbers that had extra digits, characters, etc.). And it would likely be a fairly expensive query unable to use any indexes.

A more realistic version might be this:

select * from thetable where phonenum like ('%818%123%4567%')

score 1 · Answer 4 · answered May 18 '11 at 14:01

Such kind of things is better to do in CLR or in application. But if you strongly need to do this in TSQL, so here is an example:

   DECLARE @D TABLE ( s NVARCHAR(1000), id INT )
INSERT  INTO @D
        ( s, id )
VALUES  ( '8181234$564', 1 ),
        ( '(818) 123 %&%%4564', 2 ),
        ( '818 - 123 - 4567', 3 ) ;
WITH    c ( s, Char, pos, id, Out )
          AS ( SELECT   d.s ,
                        SUBSTRING(d.s, 1, 1) ,
                        CAST(1 AS BIGINT) ,
                        d.id ,
                        CASE WHEN SUBSTRING(d.s, 1, 1) IN ( '1', '2', '3', '4',
                                                            '5', '6', '7', '8',
                                                            '9', '0' )
                             THEN CAST(SUBSTRING(d.s, 1, 1) AS NVARCHAR)
                             ELSE ''
                        END
               FROM     @d D
               UNION ALL
               SELECT   d.s ,
                        SUBSTRING(d.s, c.pos + 1, 1) ,
                        c.pos + 1 ,
                        d.id ,
                        CASE WHEN SUBSTRING(d.s, c.pos + 1, 1) IN ( '1', '2',
                                                              '3', '4', '5',
                                                              '6', '7', '8',
                                                              '9', '0' )
                             THEN CAST(c.Out + SUBSTRING(d.s, c.pos + 1, 1) AS NVARCHAR)
                             ELSE c.Out
                        END
               FROM     @d D
                        JOIN C ON c.id = d.id
               WHERE    c.pos < LEN(c.s)
             )
    SELECT  c.s [In] ,
            c.Out
    FROM    c
            JOIN ( SELECT   MAX(c2.pos) MaxPos ,
                            s
                   FROM     c C2
                   GROUP BY C2.s
                 ) CC ON cc.s = c.s
                         AND c.pos = cc.MaxPos

fantastic. I will forked the version and modified a bit for a inline table function. Will post up someday when I get a chance to work through it. thanks for sharing! — sheldonhull, Jul 11 '16 at 17:05

score 0 · Answer 5 · answered Nov 28 '16 at 21:13

Unfortunately I had not found a this thread yet; had the solution in VBA and modified it to SQL Format. Below is how to create function and sample of how to use. Get Admin to add function easiest fastest way of resolving your issue.

I use a function to clean up phone numbers which will fix all phone number issues or clears the field. Returns Null if Blank (To prevent Errors)

Print'/*Fix Phone Numbers Call*/'
Update tblTemp
    Set Phone = dbo.fnPhoneFix(tblTemp.Phone)
From tblTemp

To Create the Fuction use the following code:

CREATE FUNCTION [dbo].[fnPhoneFix](@PhoneOld VarChar(20))
    Returns VarChar(10)
AS
    Begin
    Declare @iCnt Int = 0
    Declare @PhoneNew VarChar(15)  = ''

    IF @PhoneOld IS NULL
        RETURN NULL;

    While @iCnt <= LEN(@PhoneOld)
        Begin
            IF Substring(@PhoneOld,@iCnt,1) >= '0' AND Substring(@PhoneOld,@iCnt,1) <= '9'
            Begin
                SET @PhoneNew = @PhoneNew + Substring(@PhoneOld,@iCnt,1)
            End
            Set @iCnt = @iCnt + 1
        End

    If LEN(@PhoneNew) > 10 and Left(@PhoneNew, 1) = '1'
        Set @PhoneNew = RIGHT(@PhoneNew,10);
    Else
        Set @PhoneNew = Left(@PhoneNew,10);

    Return @PhoneNew
End

SQL Server query using regex?

5 Answers5