How to format a numeric column as phone number in SQL

Question

I have table in the database with a phone number column  The numbers look like this   123456789   I want to format that to look like this   123-456-789

User · Answer

Solutions that use SUBSTRING and concatenation   are nearly independent of RDBMS  Here is a short solution that is specific to SQL Server   declare  x int   123456789 select stuff stuff  x  4  0   -    8  0   -

User · Answer

If you want to just format the output no need to create a new table or a function  In this scenario the area code was on a separate fields   I use field1  field2 just to illustrate you can select other fields in the same query   area  phone 213   8962102   Select statement   Select field1  field2 areacode phone SUBSTR tablename areacode 1 3     -    SUBSTR tablename phone 1 3     -    SUBSTR tablename areacode 4 4  as Formatted Phone from tablename   Sample OUTPUT   columns  FIELD1  FIELD2  AREA  PHONE  FORMATTED PHONE data     Field1  Field2  213   8962102   213-896-2102

User · Answer

You can also try this   CREATE  function  dbo   fn FormatPhone   Phone varchar 30    returns varchar 30  As Begin declare  FormattedPhone varchar 30   set      Phone   replace  Phone        -   --alot of entries use periods instead of dashes set  FormattedPhone       Case       When isNumeric  Phone    1 Then         case           when len  Phone    10 then     substring  Phone  1  3            substring  Phone  4  3    -   substring  Phone  7  4            when len  Phone    7  then substring  Phone  1  3    -   substring  Phone  4  4            else  Phone         end       When  phone like   0-9  0-9  0-9 - 0-9  0-9  0-9  0-9  0-9  0-9  0-9   Then     substring  Phone  1  3            substring  Phone  5  3    -   substring  Phone  8  4        When  phone like   0-9  0-9  0-9   0-9  0-9  0-9   0-9  0-9  0-9  0-9   Then     substring  Phone  1  3            substring  Phone  5  3    -   substring  Phone  9  4        When  phone like   0-9  0-9  0-9 - 0-9  0-9  0-9 - 0-9  0-9  0-9  0-9   Then     substring  Phone  1  3            substring  Phone  5  3    -   substring  Phone  9  4        Else  Phone     End return   FormattedPhone   end  use on it select   SELECT  dbo   fn FormatPhone  f coffphone   as  Phone    Output will be

User · Answer

I do not recommend keeping bad data in the database and then only correcting it on the output   We have a database where phone numbers are entered in variously as      555  555-5555 555 555 5555 555 555 5555  555 555-5555 5555555555   Different people in an organization may write various retrieval functions and updates to the database  and therefore it would be harder to set in place formatting and retrieval rules   I am therefore correcting the data in the database first and foremost and then setting in place rules and form validations that protect the integrity of this database going forward   I see no justification for keeping bad data unless as suggested a duplicate column be added with corrected formatting and the original data kept around for redundancy and reference  and YES I consider badly formatted data as BAD data

User · Answer

I d generally recommend you leave the formatting up to your front-end code and just return the data as-is from SQL  However  to do it in SQL  I d recommend you create a user-defined function to format it  Something like this   CREATE FUNCTION  dbo   fnFormatPhoneNumber   PhoneNo VARCHAR 20   RETURNS VARCHAR 25  AS BEGIN DECLARE  Formatted VARCHAR 25   IF  LEN  PhoneNo   lt  gt  10      SET  Formatted    PhoneNo ELSE     SET  Formatted   LEFT  PhoneNo  3     -    SUBSTRING  PhoneNo  4  3     -    SUBSTRING  PhoneNo  7  4   RETURN  Formatted END GO   Which you can then use like this   SELECT  dbo   fnFormatPhoneNumber  PhoneNumber  AS PhoneNumber FROM SomeTable   It has a safeguard in  in case the phone number stored isn t the expected number of digits long  is blank  null etc - it won t error   EDIT  Just clocked on you want to update your existing data  The main bit that s relevant from my answer then is that you need to protect against  dodgy  incomplete data  i e  what if some existing values are only 5 characters long

User · Answer

I found that this works if wanting in a  123  - 456-7890 format    UPDATE table  SET Phone number                                SUBSTRING Phone number  1  3                                                      -                         SUBSTRING Phone number  4  3                          -                        SUBSTRING Phone number  7  4

User · Answer

Updated  sqiller s function for my purposes  CREATE FUNCTION  toolbox   FormatPhoneNumber         PhoneNumber VARCHAR 50        DefaultIfUnknown VARCHAR 50    RETURNS VARCHAR 50  AS BEGIN     -- remove any extension     IF CHARINDEX  x    PhoneNumber  1   gt  0         SET  PhoneNumber   SUBSTRING  PhoneNumber  1  CHARINDEX  x    PhoneNumber  1  - 1       -- cleanse phone number string     WHILE PATINDEX     0-9     PhoneNumber   gt  0         SET  PhoneNumber   REPLACE  PhoneNumber                  SUBSTRING  PhoneNumber PATINDEX     0-9     PhoneNumber  1           -- Remove US international code if exists  i e  12345678900     IF SUBSTRING  PhoneNumber 1 1     1  AND LEN  PhoneNumber    11         SET  PhoneNumber   SUBSTRING  PhoneNumber  2  10       -- any phone numbers without 10 characters are set to default     IF LEN  PhoneNumber   lt  gt  10         RETURN  DefaultIfUnknown      -- build US standard phone number     SET  PhoneNumber         SUBSTRING  PhoneNumber 1 3                           SUBSTRING  PhoneNumber 4 3     -    SUBSTRING  PhoneNumber 7 4       RETURN  PhoneNumber END

User · Answer

Above users mentioned  those solutions are very basic and they won t work if the database has different phone formats like    123 123-4564 123-456-4564 1234567989 etc   Here is a more complex solution that will work with ANY input given   CREATE FUNCTION  dbo   ufn FormatPhone    PhoneNumber VARCHAR 32   RETURNS VARCHAR 32  AS BEGIN     DECLARE  Phone CHAR 32       SET  Phone    PhoneNumber      -- cleanse phone number string     WHILE PATINDEX     0-9      PhoneNumber   gt  0         SET  PhoneNumber   REPLACE  PhoneNumber  SUBSTRING  PhoneNumber  PATINDEX     0-9      PhoneNumber   1            -- skip foreign phones     IF               SUBSTRING  PhoneNumber  1  1     1              OR SUBSTRING  PhoneNumber  1  1                    OR SUBSTRING  PhoneNumber  1  1     0                        AND LEN  PhoneNumber   gt  11         RETURN  Phone      -- build US standard phone number     SET  Phone    PhoneNumber     SET  PhoneNumber         SUBSTRING  PhoneNumber  1  3           SUBSTRING  PhoneNumber  4  3     -    SUBSTRING  PhoneNumber  7  4       IF LEN  Phone  - 10  gt  1         SET  PhoneNumber    PhoneNumber     X    SUBSTRING  Phone  11  LEN  Phone  - 10       RETURN  PhoneNumber END

User · Answer

You Can Use FORMAT if you column is a number Syntax like FORMAT   value  format    culture     In use like  FORMAT    d   D    en-US    or FORMAT 123456789     -  -        But This works for only SQL SERVER 2012 And After    In Use Like  UPDATE TABLE NAME SET COLUMN NAME   FORMAT COLUMN NAME      -  -        And   if your column is Varchar Or Nvarchar use do like this CONCAT SUBSTRING CELLPHONE 0 4      SUBSTRING CELLPHONE 4 3      SUBSTRING CELLPHONE 7 2       SUBSTRING CELLPHONE 9 2     You can always get help from      https   msdn microsoft com en-us library hh213505 aspx

User · Answer

This should do it   UPDATE TheTable SET PhoneNumber   SUBSTRING PhoneNumber  1  3     -                       SUBSTRING PhoneNumber  4  3     -                       SUBSTRING PhoneNumber  7  4    Incorporated Kane s suggestion  you can compute the phone number s formatting at runtime   One possible approach would be to use scalar functions for this purpose  works in SQL Server    CREATE FUNCTION FormatPhoneNumber  phoneNumber VARCHAR 10   RETURNS VARCHAR 12  BEGIN     RETURN SUBSTRING  phoneNumber  1  3     -                SUBSTRING  phoneNumber  4  3     -                SUBSTRING  phoneNumber  7  4  END

[sql] How to format a numeric column as phone number in SQL

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2005

Examples related to tsql