I need to check if phone number is in below format or not

 1(111)111-1111
(111)111-1111
1111111111

I am using this logic but not getting what I want

DECLARE @Customers TABLE (PhoneNumber VARCHAR(20))
INSERT @Customers
VALUES
    (' 1(111)111-1111'),
    ('1111111111'),
    ('11(11)111111'),
    ('11abcd1111'),
    (')123(45678-9-0-'),
    ('(111)111-1111')

SELECT PhoneNumber
FROM @Customers
WHERE PhoneNumber NOT LIKE '%[^0-9()-]%'
--AND REPLACE(REPLACE(REPLACE(PhoneNumber, '(', ''), ')', ''), '-', '') LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
AND LEN(REPLACE(REPLACE(REPLACE(REPLACE(PhoneNumber, '(', ''), ')', ''), '-', ''),' ','')) = 10 -- Alternate test

The result I am getting

1111111111
11(11)111111
)123(45678-9-0-

UPDATE: 1(111)111-1111 should also be in the result list l but it is not. How can I add it?

CodePudding user response：

SELECT PhoneNumber
FROM @Customers
WHERE PhoneNumber LIKE replace(' 1(111)111-1111%','1','[0-9]')
   OR PhoneNumber LIKE replace('(111)111-1111%','1','[0-9]')
   OR PhoneNumber LIKE replace('1111111111%','1','[0-9]')

CodePudding user response：

My query :

DECLARE @Customers TABLE (PhoneNumber VARCHAR(20))
INSERT @Customers
VALUES
    (' 1(111)11-1111'),
    ('2222222222'),
    ('11(11)111111'),
    ('11'),
    (')123(45678-9-0-'),
    ('(111)111-1111')

SELECT PhoneNumber
FROM @Customers
WHERE 
      PhoneNumber  LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
      or PhoneNumber LIKE '[ ][0-9][(][0-9][0-9][0-9][)][0-9][0-9][-][0-9][0-9][0-9][0-9]'
      or PhoneNumber LIKE '[(][0-9][0-9][0-9][)][0-9][0-9][0-9][-][0-9][0-9][0-9][0-9]'

Output:

Let me know if you want to replace the bracket or /- sign.