Converting varchar to numeric - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Converting varchar to numeric

Author

Topic

MariaM
Starting Member

17 Posts

Posted - 2008-02-27 : 04:44:28

Hi !

I need help with the following query in wwhich I try to insert from one table where all the columns are varchar into another where three of the columns are numeric(6,2).

insert into arb_2000 (PersLopNr,PeOrgLopNr, CFARLopNr, TidochPrestLon, LonArbTid, TotLon, Sekt, SSYk )
select PersLopNr,PeOrgLopNr, CFARLopNr, cast(TidochPrestLon as numeric(6,2)), cast(LonArbTid as numeric(6,2)), cast(TotLon as numeric(6,2)), Sekt, SSYk from Arb2000

I get the following message:
Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.

I have tried both with convert and cast. Please help !

/M

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-27 : 04:48:28

Are your sure those varchar field contain only numeric data? do a check like this

select PersLopNr,PeOrgLopNr, CFARLopNr, TidochPrestLon , LonArbTid ,TotLon , Sekt, SSYk
from Arb2000
WHERE ISNUMERIC(TidochPrestLon)=0
OR ISNUMERIC(LonArbTid )=0
OR ISNUMERIC(TotLon )=0

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-27 : 04:48:48

It means some of the values in those columns are not numeric.
You can find out them with following query:

Select * from table
Where col NOT LIKE '%[^0-9]%'

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-27 : 05:25:43

quote:
Originally posted by visakh16

Are your sure those varchar field contain only numeric data? do a check like this

select PersLopNr,PeOrgLopNr, CFARLopNr, TidochPrestLon , LonArbTid ,TotLon , Sekt, SSYk
from Arb2000
WHERE ISNUMERIC(TidochPrestLon)=0
OR ISNUMERIC(LonArbTid )=0
OR ISNUMERIC(TotLon )=0

Isnumeric() is not reliable

Select data from
(
select '23487' as data
union all
select '12d4'
union all
select '$123'
) as t
where isnumeric(data)=1

Madhivanan

Failing to plan is Planning to fail

MariaM
Starting Member

17 Posts

Posted - 2008-02-28 : 09:46:52

Hi again !

I've tried with the isnumeric function, but it doesn't seem to work. It reports that some of the numbers are not numeric. Is there some other thing that I can try ?

/Mariam

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-28 : 09:51:14

quote:
Originally posted by MariaM

Hi again !

I've tried with the isnumeric function, but it doesn't seem to work. It reports that some of the numbers are not numeric. Is there some other thing that I can try ?

/Mariam

Did you read Harsh's reply and mine clearly?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources