SQL Regex get phone number

Question

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

• 9 characters (numbers)
• You can only start at 9,8,7,6

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?

Answer 1

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

Answer 2

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.