SQL: What are phone number formatting options?

SQL: What are phone number formatting options?

I query my SQL Database for a phone number:

816-123-4567

I want to output it is as:

816 123-4567.

What steps do I take to achieve this result in an SQL environment?

Answer

A standard SQL solution would be:

select substring(phone, 1, 3) || ' ' || substring(phone, 5, 8)

There may be simpler solutions in other databases. For instance, in SQL Server:

select stuff(phone, 4, 1, ' ')

And in MySQL:

select insert(phone, 4, 1, ' ')

Note: These are specific the the format you have provided in your question. If you have other formats, then you might need more complicated logic.