CODEWITHSUNDEEP
sqlsql-serversql-server-2008-r2sql-server-2012
CodeNinja
CodeNinja

Reputation: 3278

Converting varchar to numeric type in SQL Server

I have a column in a table with a varchar datatype. It has 15 digits after the decimal point. Now I am having a hard time converting it to a numeric format.. float, double etc.

Does anyone have any suggestions?

Example :

Table1 

    Column1
-------------------
-28.851540616246499
-22.857142857142858
-26.923076923076923
76.19047619047619

I tried using the following statements and it doesn't seem to work :

update table1
set Column1 = Convert(float,column1)..

Any suggestions ?

Upvotes: 1

Views: 78611

Answers (2)

Tanner
Tanner

Reputation: 22733

You can use the decimal data type and specify the precision to state how many digits are after the decimal point. So you could use decimal(28,20) for example, which would hold 28 digits with 20 of them after the decimal point.

Here's a SQL Fiddle, showing your data in decimal format.

Fiddle sample:

create table Table1(MyValues varchar(100))

insert into Table1(MyValues)
values                    
('-28.851540616246499'),
('-22.857142857142858'),
('-26.923076923076923'),
('76.19047619047619')

So the values are held as varchar in this table, but you can cast it to decimal as long as they are all valid values, like so:

select cast(MyValues as decimal(28,20)) as DecimalValues 
from table1

Your Sample

Looking at your sample update statement, you wouldn't be able to convert the values from varchar to a numeric type and insert them back in to the same column, as the column is of type varchar. You would be better off adding a new column with a numeric data type and updating that.

So if you had 2 columns:

create table Table1(MyValues varchar(100), DecimalValues decimal(28,20))

You could do the below to update the numeric column with the nvarchar values that have been cast to decimal:

update Table1
set DecimalValues = cast(MyValues as decimal(28,20))

Upvotes: 4

Brian DeMilia
Brian DeMilia

Reputation: 13233

I think you're trying to actually change the data type of that column?

If that is the case you want to ALTER the table and change the column type over to float, like so:

alter table table1
alter column column1 float

See fiddle: http://sqlfiddle.com/#!6/637e6/1/0

You would use CONVERT if you're changing the text values to numbers for temporary use within a query (not to actually permanently change the data).

Upvotes: 1

Related Questions