CODEWITHSUNDEEP
sql-serverregex
csotelo
csotelo

Reputation: 1485

SQL Regex get phone number

I want to get the phone number of a text string that meets certain conditions:

I tried with the expression: 

'^\(?(9|8|7|6)\)?([0-9]{8}))$'

in the following function:

DECLARE @str VARCHAR(MAX)
DECLARE @validchars VARCHAR(MAX)

SET @str = 'TESTING1234 997711056'
SET @validchars = '^\(?(9|8|7|6)\)?([0-9]{8}))$' 

WHILE PATINDEX('%' + @validchars + '%',@str) > 0
SET @str=REPLACE(@str, SUBSTRING(@str ,PATINDEX('%'+ @validchars +'%',@str), 1) ,'')


SELECT @str

I need to get 997711056 but I always get the complete text string. What is my mistake?

Upvotes: 4

Views: 24765

Answers (2)

Ivan Golović
Ivan Golović

Reputation: 8832

Try with this:

DECLARE @str VARCHAR(MAX) 

---- returns 997711056
SET @str= 'TESTING1234 997711056'
---- returns NULL
--SET @str= 'TESTING1234 0997711056'
---- returns NULL
--SET @str= 'TESTING1234 9977110560'
---- returns 997711056
--SET @str= '997711056'
---- returns NULL
--SET @str= '0997711056'
---- returns NULL
--SET @str= '9977110560'
---- returns NULL
--SET @str= 'test'

DECLARE @pat NVARCHAR(100) = '%[6-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%'
DECLARE @len INT = 9
DECLARE @ind INT = PATINDEX(@pat, @str)

SELECT  CASE WHEN
            ISNUMERIC(SUBSTRING(@str, @ind - 1, 1)) = 1 OR ISNUMERIC(SUBSTRING(@str, @ind + @len, 1)) = 1 THEN NULL 
        ELSE
            SUBSTRING(@str, NULLIF(@ind, 0), @len)
        END

PATINDEX is not intended to match regular expressions, for it's usage read the MSDN article about it

If you just want to extract the number you don't need the WHILE loop.

For the possibility when there is no 9-digit number starting with numbers 6-9 in the string, this code will return NULL.

Upvotes: 2

Esoteric Screen Name
Esoteric Screen Name

Reputation: 6112

PATINDEX is not a regex function. You can only use a limited set of pattern identifiers and wildcards with it. From MSDN:

% : Any string of zero or more characters.
_ (underscore) : Any single character.
[] : Any single character within the specified range ([a-f]) or set ([abcdef]).
[^] : Any single character not within the specified range ([^a-f]) or set ([^abcdef]).

The following will match nine digits in a row, starting with 6, 7, 8, or 9:

SET @validchars = '[6-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'

Your use of SUBSTRING is also incorrect. It looks like your code is attempting to eliminate characters of the number from the string. Here is how to get the string with the number removed:

DECLARE @str VARCHAR(MAX)
DECLARE @validchars VARCHAR(MAX)

SET @str = 'TESTING1234 997711056 sdfgdfg'
SET @validchars = '[6-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
DECLARE @idx INT
SET @idx = PATINDEX('%'+ @validchars +'%',@str)
IF @idx > 0 AND 
    (@idx = LEN(@str)-8
    OR PATINDEX(SUBSTRING(@str,@idx+9,1),'[0-9]')=0)
    SET @str=REPLACE(@str,SUBSTRING(@str ,PATINDEX('%'+ @validchars +'%',@str), 9),'')
ELSE SET @str = ''
SELECT @str

You state you want to retrieve just the number out of the string; here is how to do that:

DECLARE @str VARCHAR(MAX)
DECLARE @validchars VARCHAR(MAX)

SET @str = 'TESTING1234 997711056 sdfgdfg'
SET @validchars = '[6-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
DECLARE @idx INT
SET @idx = PATINDEX('%'+ @validchars +'%',@str)
IF @idx > 0 AND 
    (@idx = LEN(@str)-8
    OR PATINDEX(SUBSTRING(@str,@idx+9,1),'[0-9]')=0)
    SET @str=SUBSTRING(@str ,PATINDEX('%'+ @validchars +'%',@str), 9)
ELSE SET @str = ''
SELECT @str

Upvotes: 6

Related Questions