CODEWITHSUNDEEP
sqlsql-servercopysql-types
user2110298
user2110298

Reputation: 567

SQL Converting Column type from float to varchar

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:

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.

Upvotes: 9

Views: 27679

Answers (3)

Arvind Naladiga Venkat
Arvind Naladiga Venkat

Reputation: 41

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

Upvotes: 0

Nickthename
Nickthename

Reputation: 259

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

Upvotes: -1

Gordon Linoff
Gordon Linoff

Reputation: 1270693

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.

Upvotes: 12

Related Questions