Solved

SQL Script to alter data format

Posted on 2013-06-10
3
307 Views
Last Modified: 2013-06-10
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
Comment
Question by:fireprograms_tech
3 Comments
 
LVL 51

Expert Comment

by:HainKurt
ID: 39235968
select replace ('999-99-9999','-','')
999999999
0
 
LVL 10

Accepted Solution

by:
Banthor earned 500 total points
ID: 39235978
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
 

Author Closing Comment

by:fireprograms_tech
ID: 39236069
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

856 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