SQL Modify phone numbers in fields

Posted on 2012-09-13
Last Modified: 2012-09-13
Hi there,

I'm looking for help with the SQL code to modify a phone numbers in a column. I'm not sure what to use.

Right now they are like this:


And I would like them to be like this:

(234) 555-2323
(234) 555-1234
(234) 555-2341

Question by:PrimusPilus
    LVL 75

    Assisted Solution

    by:Aneesh Retnakaran
    declare @str varchar(100 ) = '250-516-4499'
    select '('+STUFF(@str,4,1,')' )
    LVL 68

    Accepted Solution

    I'd suggest adding a WHERE condition so you can re-run the statement if needed but still not re-do a number already reformatted.

    UPDATE dbo.tablename
        phone_number =  '(' + LEFT(phone_number, 3) + ') ' + SUBSTRING(phone_number, 5, 100)
        --_ is any char; [-] is exactly a -; % is match anything
        phone_number LIKE '___[-]___[-]____%'

    Author Comment

    Thank you guys!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
    If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

    758 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now