convert float to varchar datatype

Question

convert float to varchar datatype

kreskins

Default port

Points: 1429
More actions
November 20, 2002 at 9:40 am

#80407

Is it possible to change the datatype of one column which is a float (8) datatype to varchar (5) datatype. There are 5000 records of data in this field. I would like to change all entries and make sure all subsequent entries are of the varchar type.
Thanks For your continued Help.

Thanks For your continued Help.

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Greg Larsen SSC-Insane Points: 20961 More actions · Answer 1

Maybe. If your float data is less then 6 characters long you should have no problem. Now if it is not, you will need to update all the fields that are longer than 5 to be 5 or less characters long prior to making the change. Understand if you make this change all code referencing this field will need to be review to make sure it will handle a varchar, instead of a float.

Gregory Larsen, DBA

If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

Gregory A. Larsen, MVP

kreskins Default port Points: 1429 More actions · Answer 2

the data in the column consists of numbers that are AT MOST 4 char long. If I were to convert all to varchar what command would I use. Alter Table or Convert? I am not sure

Thanks For your continued Help.

Greg Larsen SSC-Insane Points: 20961 More actions · Answer 3

One method would be to go into EM, hightlight the table, right click and choose "Design Table". Or you could issue the something like:

ALTER TABLE <yourtable> ALTER COLUMN <yourfloatcolumn> VARCHAR(5)

Gregory Larsen, DBA

If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

Gregory A. Larsen, MVP

kreskins Default port Points: 1429 More actions · Answer 4

Darn....I am sorry Gary, I failed to mention that I am using SQL server 6.5 still. That is why I am struggling with this task. I don't think that 6.5 supports ALTER COLUMN

Thanks For your continued Help.

Simon Sabin SSCrazy Eights Points: 8142 More actions · Answer 5

ALTER TABLE TableName ALTER COLUMN ColumnName varchar(5)

Simon Sabin

Co-author of SQL Server 2000 XML Distilled

http://www.amazon.co.uk/exec/obidos/ASIN/1904347088

Simon Sabin
SQL Server MVP

http://sqlblogcasts.com/blogs/simons

Simon Sabin SSCrazy Eights Points: 8142 More actions · Answer 6

You'll have to rename the table,

drop the constraints

create the new table

then insert the data back into the new table from the old table.

Finally recreate the constraints

Simon Sabin

Co-author of SQL Server 2000 XML Distilled

http://www.amazon.co.uk/exec/obidos/ASIN/1904347088

Simon Sabin
SQL Server MVP

http://sqlblogcasts.com/blogs/simons

Greg Larsen SSC-Insane Points: 20961 More actions · Answer 7

And I don't have a 6.5 server anymore, and my memory doesn't recall how to do it in 6.5.

I'm sure some other individual will help you out.

One thought would be to copy the data to a temporary table, and then drop and recreate your table, then take the data from the temporary table and place it back into the newly define table.

Gregory Larsen, DBA

If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

Gregory A. Larsen, MVP

kreskins Default port Points: 1429 More actions · Answer 8

what are the steps to drop the constraints?

Thanks For your continued Help.

Greg Larsen SSC-Insane Points: 20961 More actions · Answer 9

I'm not sure I should be offer advice, but in 2000 I would do this:

exec sp_helpconstraint <tablename>

and then for each constraint identified I would issue an alter table drop constraint <constraint name>

Gregory Larsen, DBA

If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

Gregory A. Larsen, MVP

kreskins Default port Points: 1429 More actions · Answer 10

Thanks Greg...I was able to find out that there are NO constraints for my table. If this is the case I should be able to just insert the data into a new table with the correct datatype according to Simon's above post. When doing this I get the following error:

Msg 260, Level 16, State 1

Disallowed implicit conversion from datatype 'float null' to datatype 'varchar'

Table: 'FDIS_SECURITY.dbo.FDIS_SECURITY2', Column: 'upgroup'

Use the CONVERT function to run this query.

Thanks For your continued Help.

Greg Larsen SSC-Insane Points: 20961 More actions · Answer 11

You will need to use the convert you float data to varchar when selecting your float data. Should be something like this

cast(float_field as varchar(5))

Gregory Larsen, DBA

If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

Gregory A. Larsen, MVP

kreskins Default port Points: 1429 More actions · Answer 12

Thanks for all the help...there is no "cast" in 6.5 I am just going to re-create the column and re-enter the data...this is way too time consuming. I really am frustrated with 6.5. I appreciate all of your time Greg

Thanks For your continued Help.

Simon Sabin SSCrazy Eights Points: 8142 More actions · Answer 13

you can use the CONVERT function in 6.5.

Simon Sabin

Co-author of SQL Server 2000 XML Distilled

http://www.amazon.co.uk/exec/obidos/ASIN/1904347088

Simon Sabin
SQL Server MVP

http://sqlblogcasts.com/blogs/simons

Babou SSC Enthusiast Points: 137 More actions · Answer 14

Why do you people use STR Function to convert numeric values to String values.

Bakthi Margan