converting varchar to float - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

converting varchar to float

Author

Topic

IA
Starting Member

9 Posts

Posted - 2007-08-22 : 04:01:49

Hi all,

I'm trying to update a table as follows:
Where in the table i got.
OldCol; a varchar
NewCol; Float

Update tbl set newCol = cast(float,oldcol)
Where isNumeric(oldcol) =1

But it give me the follwong error:
Error converting data type from Varchar to Float.

Any suggestion?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-22 : 04:10:15

ISNUMERIC is not reliable, becuase it allows some characters in the numeric check.

What kind of varchar "numeric" do you have? Are they all supposed to be integers?

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-22 : 04:17:34

Instead of

Where isNumeric(oldcol) =1

use

Where oldcol not like '%[^0-9.]%'

Madhivanan

Failing to plan is Planning to fail

IA
Starting Member

9 Posts

Posted - 2007-08-22 : 04:23:12

NO, not integers but with decimal

quote:
Originally posted by Peso

ISNUMERIC is not reliable, becuase it allows some characters in the numeric check.

What kind of varchar "numeric" do you have? Are they all supposed to be integers?

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-22 : 04:26:28

quote:
Originally posted by IA

NO, not integers but with decimal

quote:
Originally posted by Peso

ISNUMERIC is not reliable, becuase it allows some characters in the numeric check.

What kind of varchar "numeric" do you have? Are they all supposed to be integers?

E 12°55'05.25"
N 56°04'39.16"

Try my suggestion
Can you post some sample data?

Madhivanan

Failing to plan is Planning to fail

IA
Starting Member

9 Posts

Posted - 2007-08-22 : 04:41:10

I used what you suggested, but it does not update any record.

0 Record updated

Sample data:

4,65
10,5
9,66
0,0

quote:
Originally posted by madhivanan

Instead of

Where isNumeric(oldcol) =1

use

Where oldcol not like '%[^0-9.]%'

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-22 : 04:50:02

So did you store comma sepeated values in a column?

Madhivanan

Failing to plan is Planning to fail

IA
Starting Member

9 Posts

Posted - 2007-08-22 : 04:59:44

As it show in samples

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-22 : 05:32:48

Well. which value do you want to take? eg from 4,65 do you need 4 or 65?

Madhivanan

Failing to plan is Planning to fail

IA
Starting Member

9 Posts

Posted - 2007-08-22 : 05:36:08

I need all as it's in the oldcol. But anyay it seems imposible like that. I remove the comma then it works.

sbalaji
Starting Member

48 Posts

Posted - 2007-08-22 : 06:23:54

Update tbl
set newCol = cast(replace(oldcol,',','.') as float)
Where isNumeric(oldcol) =1

i think this should work,I presume u r using comma separator to denote decimal values instead of dot.

IA
Starting Member

9 Posts

Posted - 2007-08-22 : 06:31:10

Yes, it did work, thank you very much.
Cheers

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-22 : 06:33:07

quote:
Originally posted by IA

Yes, it did work, thank you very much.
Cheers

Dont store formatted data in a table

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources