SQL: What are phone number formatting options?

user_14674009 0
Viewed 80

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?

sql
2 Answers

Use SUBSTRING and CHARINDEX functions.

SUBSTRING gets a part of the string according to given indexes.

CHARINDEX gets the index of the character that triggers your String separation.

Below is an MSSQL Server query. According to your DBMS, the function names can vary, but the logic will is the same.

Query :

SELECT
SUBSTRING('816-123-4567', 0, CHARINDEX('-', '816-123-4567') ) + 

' ' +

SUBSTRING('816-123-4567', CHARINDEX('-', '816-123-4567') + 1 , LEN('816-123-4567') - 
CHARINDEX('-', '816-123-4567') )

And the result :

816 123-4567

When we put your field and table name instead of static values :

SELECT
SUBSTRING(YourPhoneField, 0, CHARINDEX('-', YourPhoneField) ) + 

' ' +

SUBSTRING(YourPhoneField, CHARINDEX('-', YourPhoneField) + 1 , LEN(YourPhoneField) - 
CHARINDEX('-', YourPhoneField) )

FROM YourTableName
user_18290480

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.

user_11440350
Related