CAST CONVERT This should not be too difficult

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

CAST CONVERT This should not be too difficult

Author

Topic

richardg
Starting Member

14 Posts

Posted - 2011-02-22 : 17:55:21

I have imported a set of phone (and fax) numbers from Excel. I saved the phone number columns in every conceivable format: General, Number (w/o decimals), text, phone number format and so on. When I import the Excel into a SQL table and open the newly created table, the columns for phones and fax look great. And it doesn't matter how it is saved in Excel.

Next step is to import data into an existing table and the phone numbers look like this:
9.13371e+009
7.04598e+009
4.01739e+009
2.06424e+009
2.04416e+009
7.15235e+009
5.13229e+009
2.56236e+009
8.12219e+009
and so on.

I am looking for a way to CAST or CONVERT the data in the existing (not the imported) table so that it looks like this 1234567890.
(The program using it knows to convert it to (123)456-7890).

What is the syntax to convert THIS format: 8.12219e+009 into this format: 1234567890 ??

I have looked at the SQL Help page but have found nothing useful

Thanks!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-02-22 : 18:11:02

If they imported into SQL in that format then you can't reconstruct it. There's an Excel function called TEXT() that lets you format numbers as strings with the precise layout you need. It's NOT the same as a column/cell format, which won't help you anyway as it doesn't change the internal format it's stored in.

For future reference, make sure to import phone numbers, zip codes, and other numeric strings as text. Once Excel converts them to numbers you may lose information that formatting can't recover.

richardg
Starting Member

14 Posts

Posted - 2011-02-22 : 18:44:43

Thanks. I am not sure I know the Syntax for changing the columns in Excel. What I did was to highlight the Phone and Fax columns, right-click, select Format Cells and then Text. But that resulted in the same thing!
Where do you run the Excel function called TEXT() ?
I am not sure if you do that IN Excel, or if you gave to go into VB to do that?

Thanks for your help!

R

quote:
Originally posted by robvolk

If they imported into SQL in that format then you can't reconstruct it. There's an Excel function called TEXT() that lets you format numbers as strings with the precise layout you need. It's NOT the same as a column/cell format, which won't help you anyway as it doesn't change the internal format it's stored in.

For future reference, make sure to import phone numbers, zip codes, and other numeric strings as text. Once Excel converts them to numbers you may lose information that formatting can't recover.

richardg
Starting Member

14 Posts

Posted - 2011-02-22 : 19:26:16

I got it!!

thanks!

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-23 : 00:20:27

also, this happens when there's change in datatype of the column at source and destination. Try the same when datatypes are same same on both ends.

Cheers
MIK

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources