How to Format a Numeric Column as Phone Number in SQL

How to format a numeric column as phone number in SQL

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

Formatting Phone Number to US Format (###) ###-####

Since one suggestion was made already and the suggestion there to isolate numbers in a string uses a while loop I need to post an alternative to that which doesn't use any looping. Instead it utilizes a tally or numbers table. There are lots of solutions for those. I like to use a view which is lightning fast and has zero reads.

Here is my version of a tally table.

create View [dbo].[cteTally] as

WITH
    E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
    E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
    E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
    cteTally(N) AS 
    (
        SELECT  ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
    )
select N from cteTally

Next we need a table valued function to remove the characters that are not numbers using our tally table. This is also super fast because we are using our tally table instead of looping.

create function GetOnlyNumbers
(
    @SearchVal varchar(8000)
) returns table as return

    with MyValues as
    (
        select substring(@SearchVal, N, 1) as number
            , t.N
        from cteTally t 
        where N <= len(@SearchVal)
            and substring(@SearchVal, N, 1) like '[0-9]'
    )

    select distinct NumValue = STUFF((select number + ''
                from MyValues mv2
                order by mv2.N
                for xml path('')), 1, 0, '')
    from MyValues mv

Now that we have all the legwork done we can focus on the task at hand. Since you didn't provide any sample data I just made up some stuff. I am not really sure if this is representative of your data or not but this works on the sample data I created.

if OBJECT_ID('tempdb..#Something') is not null
    drop table #Something

create table #Something(SomeVal varchar(100))

insert #Something values
('Maybe you have other stuff in here. 5552223333 additional characters can cause grief')
, ('321-654-9878')
, ('123)-333-4444')
, ('1234567')

select replace(format(try_convert(bigint, n.NumValue), '(###) ###-####'), '() ', '')
    , n.NumValue
from #Something s
cross apply dbo.GetOnlyNumbers(s.SomeVal) n

The output for the formatted data looks like this:

(555) 222-3333
(321) 654-9878
(123) 333-4444
123-4567

How to convert phone numbers into given format?

FORMAT SQL Server (starting with 2012)
https://learn.microsoft.com/en-us/sql/t-sql/functions/format-transact-sql

SELECT FORMAT(606548795,'+420 000 000 000')

What datatype should be used for storing phone numbers in SQL Server 2005?

Does this include:

• International numbers?
• Extensions?
• Other information besides the actual number (like "ask for bobby")?

If all of these are no, I would use a 10 char field and strip out all non-numeric data. If the first is a yes and the other two are no, I'd use two varchar(50) fields, one for the original input and one with all non-numeric data striped and used for indexing. If 2 or 3 are yes, I think I'd do two fields and some kind of crazy parser to determine what is extension or other data and deal with it appropriately. Of course you could avoid the 2nd column by doing something with the index where it strips out the extra characters when creating the index, but I'd just make a second column and probably do the stripping of characters with a trigger.

Update: to address the AJAX issue, it may not be as bad as you think. If this is realistically the main way anything is done to the table, store only the digits in a secondary column as I said, and then make the index for that column the clustered one.

Format Phone Numbers With Characters

You can try creating one more function to clean up any trailing number that comes after alphabets.

First, clean up any special character.
Second, clean trailing numbers and alphabets.

1. Function to remove Special Characters

CREATE Function [dbo].[RemoveSpecialCharacters](@Temp VarChar(1000))
Returns VarChar(1000)
AS
Begin
    Declare @KeepValues as varchar(50)
    Set @KeepValues = '%[^0-9A-Za-z]%'
    While PatIndex(@KeepValues, @Temp) > 0
        Set @Temp = Stuff(@Temp, PatIndex(@KeepValues, @Temp), 1, '')

    Return @Temp
End

2. Function to remove trailing numbers and alphabets

CREATE Function [dbo].[RemoveExtraCharacters](@Temp VarChar(1000))
Returns VarChar(1000)
AS
Begin
    Declare @KeepValues as varchar(50)
    Set @KeepValues = '%[^0-9]%'
    While PatIndex(@KeepValues, @Temp) > 0
        -- Here you specify the length of text that you need to remove len(@Temp)-PatIndex(@KeepValues, @Temp)+1
        Set @Temp = Stuff(@Temp, PatIndex(@KeepValues, @Temp), len(@Temp)-PatIndex(@KeepValues, @Temp)+1, '')

    Return @Temp
End

Then you can call function on your column.

SELECT [dbo].[RemoveExtraCharacters]([dbo].[RemoveSpecialCharacters](PhoneNo)) FROM Yourtable

Edit:

This will remove the country code.

SELECT CASE WHEN
    LEN([dbo].[RemoveExtraCharacters]([dbo].[RemoveSpecialCharacters](PhoneNo)))>10 THEN 
        RIGHT([dbo].[RemoveExtraCharacters]([dbo].[RemoveSpecialCharacters](PhoneNo)), 10)
    ELSE 
        [dbo].[RemoveExtraCharacters]([dbo].[RemoveSpecialCharacters](PhoneNo))
    END AS PhoneNo
FROM Yourtable

You can see example here

Format phone number to international

I'm guessing that you are using MySQL. The way to concatenate strings is concat():

UPDATE vtiger_contactdetails
    SET phone = CONCAT('+49', ' ', SUBSTRING(phone, 2, 2 ), ' ', SUBSTRING(phone, 4, 3));

To get all the characters, just use two arguments to SUBSTRING():

UPDATE vtiger_contactdetails
    SET phone = CONCAT('+49', ' ', SUBSTRING(phone, 2, 2 ), ' ', SUBSTRING(phone, 4));

Note that this works in MySQL, but not in all databases.

How to store a phone number in SQL as an integer?

Don't do that, phone numbers are not integers for very good reasons :

• leading zeroes : 0199857514
• country codes : +34199857514
• special commands : #992,514
• sip phones : sip:01199857514@stackoverflow.net

If you're going to store your phone numbers as integers, you are going to have major limitations and problems later.

To answer your question: You can't store a phone number as an integer, you must use a string.

formating a phone number in the postresql query

Use SUBSTRING function

something like:

  SELECT 
 '(' || SUBSTRING((PhoneNumber, 1, 3) + ') ' || SUBSTRING(PhoneNumber, 4,3) || '-' || SUBSTRING((PhoneNumber,7,4)

---

Related Topics