How to Group By row format / regex in SQL?-CodePudding

Let's say I have different formats of phone numbers stored as strings in my database:

Phone
(812) 283-2013
701 857 7000
207-623-0389
5592369797
...

Is it possible to list a sample of each format that I have, so I would know which formats exist?

I'm using SQL Server but I'm interested in the general idea, so the approach for any other relational DB is fine.

CodePudding user response：

If I understand correctly, you are looking for distinct patterns of phone numbers. If so, translate() would be a good option

Declare @YourTable Table ([Phone] varchar(50))  Insert Into @YourTable Values 
 ('(812) 283-2013')
,('701 857 7000')
,('207-623-0389')
,('5592369797')
 

Select distinct Frmat = translate(Phone,'0123456789','##########') 
 from @YourTable

Results

Frmat
### ### ####
##########
###-###-####
(###) ###-####