SQL Converting Column type from float to varchar

Question

I'm trying to change the datatype in a column in a table from Float (null) to Varchar(25) (null). The largest float in the current data is 12 digits, but more digits may have to be added in the future, hence varchar(25).

The current data in the column is phone numbers. The change must be made to allow for preceding zeros.

However, I'm having some difficulties doing this.

I've tried the following:

ALTER TABLE Customer 
ALTER COLUMN Phonenumber varchar(25)

This does not give me the desired result.

For instance 1549779498 becomes 1.54978e+009

Then I've tried something in the lines of this:

• Creating a new (temporary) column PhonenumberVarchar
• Converting and copying the data from one column to another
• Deleting the old column
• Renaming the new column to the old name

Code:

ALTER TABLE Customer 
ADD PhonenumberVarchar varchar(25)

UPDATE Customer 
SET PhonenumberVarchar = STR(Phonenumber, 12, 0)

ALTER TABLE Customer 
DROP COLUMN Phonenumber

EXEC sp_rename 'Customer.PhonenumberVarchar', 'Phonenumber', 'COLUMN'

This doesn't work either:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

And now it's late and my head hurts...

Can anyone help?

NOTE:

The table is rather large, some 1.5 million rows, so performance can be an issue.

Using SQL Server.

Answer 1

You can fix this by going through a decimal first:

ALTER TABLE Customer ALTER COLUMN Phonenumber decimal(25, 0);
ALTER TABLE Customer ALTER COLUMN Phonenumber varchar(25);

You get the same behavior when using cast():

select cast(cast(1549779498 as float) as varchar(255))

So the fix is illustrated by:

select cast(cast(cast(1549779498 as float) as decimal(25)) as varchar(255))

The documentation for alter table alter column explicitly references cast():

Some data type changes may cause a change in the data. For example, changing an nchar or nvarchar column to char or varchar may cause the conversion of extended characters. For more information, see CAST and CONVERT (Transact-SQL). Reducing the precision or scale of a column may cause data truncation.

EDIT:

After you load the data, I would suggest that you also add a check constraint:

check (PhoneNumber not like '%[^0-9]%')

This will ensure that numbers -- and only numbers -- remain in the column in the future.