CODEWITHSUNDEEP
sqlsql-serverregext-sql
vick
vick

Reputation: 476

SQL Server query using regex?

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.

Upvotes: 6

Views: 3063

Answers (5)

Crazyd
Crazyd

Reputation: 416

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

Upvotes: 0

Dalex
Dalex

Reputation: 3625

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

Upvotes: 1

Mark Wilkins
Mark Wilkins

Reputation: 41262

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%')

Upvotes: 1

Shane Castle
Shane Castle

Reputation: 1759

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,'(',''),' ',''),')',''),'-','')

Upvotes: 2

IAmTimCorey
IAmTimCorey

Reputation: 16755

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.

Upvotes: 1

Related Questions