Member Avatar for freshfitz

Can I reformat a phone number in SQL manager. I imported a foxpro dbf with the dts wizard and it imported the phone number as (609) 555-1212 I need it to be 6095551212

  • 2 Contributors
  • 2 Replies
  • 103 Views
  • 1 Month Discussion Span
  • Latest Post Latest Post by freshfitz

Dani AI

Generated

As pointed out, a quick string-replace is a perfectly valid one-off. later mentioned a stored procedure that solved it. For longer-term or large-scale work, consider normalizing phone data during import or using a single, set-based transform rather than many nested replaces.

For modern SQL Server (2017+), use TRANSLATE to map unwanted characters to spaces and then remove the spaces in one pass:

UPDATE dbo.YourTable
SET PhoneDigits = REPLACE(
    TRANSLATE(PhoneRaw, '()- .+', '      '),
    '','')

(Adjust the first string to include every punctuation you expect; the replacement string must be the same length. The outer REPLACE then strips the placeholder characters.)

For older servers or when you need to keep everything T-SQL, an inline routine using a tally/row generator plus FOR XML PATH() extracts digits only in a set-based way. Wrap it as a scalar or inline TVF and call it in an UPDATE to populate a cleaned column.

Operational tips:

  • Keep the original column. Add a new cleaned column (or a persisted computed column) so you can revert if needed.
  • Store phone numbers as text (CHAR/VARCHAR). Avoid numeric types (they drop leading zeros and plus signs).
  • Decide format policy: local 10-digit strings, or normalized E.164 (recommended for multi-country data).
  • Add a CHECK constraint to enforce digits-only on the cleaned column and index that column if you query by phone.
  • Test on a subset and watch NULLs, extensions (e.g., “ext 123”), and international prefixes; handle or document them before mass updates.

For a one-time import fix, an ETL transform (during import) is cleaner; for ongoing data quality, use a persisted computed column or a routine that runs on insert/update.

Member Avatar for daggerpound

Can I reformat a phone number in SQL manager. I imported a foxpro dbf with the dts wizard and it imported the phone number as (609) 555-1212 I need it to be 6095551212

Try Nested REPLACE Functions
------------------------------

SELECT  REPLACE(REPLACE(REPLACE(REPLACE('(800) 555-1212', '(', ''), ')', ''), ' ', ''), '-', '')

------------------------------
copy and paste this and change the literal phone number to your field name or variable.

Member Avatar for freshfitz

i DID find a stored procedure that worked but thanks, I keep this with my pile of scripts

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.