Error coverting varchar to numeric

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

SQL Server Administration (2000)

Error coverting varchar to numeric

Author

Topic

krishmeena
Starting Member

17 Posts

Posted - 2005-09-20 : 14:04:46

Sorry for a basic question....\\
How to execute this type of statement without conversion error?

select convert(decimal(10,3),'NA')

I have some varchar column with NA and I need to insert
it into another table which has the same column set as
int/decimal

Thanks a ton

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-09-20 : 14:11:03

emm... you can't...
you can't just insert varcahr datatype into a decimal column.

Go with the flow & have fun! Else fight the flow

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-09-20 : 14:11:19

does it have other values??

like
10.012
384.932
NA
2302.21

???

if so then
Select convert(decimal(10,3),nullif(<yourColName>,'NA')) From <yourTableName>

Corey

Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now."

Kristen
Test

22859 Posts

Posted - 2005-09-20 : 14:11:38

Well ... the "string" you are converting to DECIMAL needs to be a valid decimal, otherwise you'll get an error.

If you are happy that illegal values are set to NULL then you might be able to do:


DECLARE @strValue varchar(1000)
SET @strValue = 'NA'
SELECT convert(decimal(10,3), 
                 CASE WHEN ISNUMERIC(@strValue) = 0 THEN NULL ELSE @strValue END
              )

But ISNUMERIC tends not to be bullet-proof.

Kristen

Kristen
Test

22859 Posts

Posted - 2005-09-20 : 14:12:41

Blimey, a