• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 315
  • Last Modified:

SQL Script to alter data format

Currently have a column in the database that is char (20) in the following format
999-99-9999 to represent a phone number however this value needs to be imported into another database where the format is char (13) and shown as 999999999

Is there a sql script that can be run to remove the dashes from the column or to copy just the numbers from this column to a new column>
0
fireprograms_tech
Asked:
fireprograms_tech
1 Solution
 
HainKurtSr. System AnalystCommented:
select replace ('999-99-9999','-','')
999999999
0
 
BanthorCommented:
There are too many possible answers.
update Table set [telcol] = replace([telcol],'-','') Will remove the dashs but will not validate the output.
you can use the statement as a select

SELECT
  replace([telcol],'-','')
from
  Table

and locate issues as

SELECT telcol, *
 from table
where 1=0
  or LEN(replace([telcol],'-','')   !=  9
  or isnumeric(replace([telcol],'-','') ) = 0
0
 
fireprograms_techAuthor Commented:
Thank you so much this completely resolved the issue I was having with the database,  I was able to create a new column and then run the update formula and move the edited data to the new column.  Thanks again!
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now