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.

Depressomotor

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.

Entertainment

This seems to work. So if I understand correctly the trick with the decimal conversion first is to make sure the rounding errors which I got using nvarchar directly are eliminated? – Depressomotor

@Depressomotor . . . Yes, although I'm not sure they are exactly rounding errors. The representation used for the conversion is not what you want, and this is a relatively simple way around that issue. – Entertainment

Answer 2

Direct float to varchar conversions can be tricky. Merely altering column data type wont be sufficient.

STEP 1: Take backup of your data table.

SELECT * INTO Customer_Backup FROM Customer

STEP 2: Drop and Create your original data table using SQL Server Scripts // OR // DROP and Alter the data type of the column

ALTER TABLE Customer 
ALTER COLUMN Phonenumber varchar(25)

STEP 3: In you scenario, since phone numbers don't have decimal data values in the float column, we can convert it first to int and then to varchar like below

INSERT into Customer (Phonenumber) 
SELECT convert (varchar(25), convert(int, [Phonenumber])) as [Phonenumber]
FROM Customer_Backup

Pearlene

Answer 3

ALTER TABLE Customer  ADD PhonenumberVarchar  VARCHAR(25)

GO

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

ALTER TABLE Phonenumber  ALTER COLUMN Phonenumber  VARCHAR(25)

UPDATE Customer  SET Phonenumber = PhonenumberVarchar 

ALTER TABLE Customer  DROP COLUMN PhonenumberVarchar 

Crustacean

Using this code still gets the described error in the original post. – Depressomotor