CODEWITHSUNDEEP
sqlsql-server
john43
john43

Reputation: 1

How do I get the area code from phone numbers using SQL?

I have a table that has a column with 1 to 3 digits. For example:

  1. (342) 342-9324
  2. (1) 234-3424
  3. (04) 234-7744 etc

But I am not sure how to write the query. I use

SUBSTRING(x, 2, 3)

where x is name of column, but I only get 3 digits, anyone have any ideas to extract digits in brackets that could be 1, 2 or 3 digits? This is done using sql server. Also this table has more than 5 million rows of phone numbers

Upvotes: 0

Views: 974

Answers (1)

John Cappelletti
John Cappelletti

Reputation: 81990

If area code is within (), some simple string functions should do.

Example

Declare @YourTable Table ([Phone] varchar(50))  Insert Into @YourTable Values 
 ('(342) 342-9324')
,('(1) 234-3424')
,('(04) 234-7744')
 
Select * 
      ,AreaCode = replace(left(Phone,charindex(')',Phone+')')-1),'(','')
 From @YourTable

Returns

Phone           AreaCode
(342) 342-9324  342
(1) 234-3424    1
(04) 234-7744   04

Upvotes: 2

Related Questions