Changing VarChar to Number

[Crystalguru]

Hello,

I need to convert a VARCHAR(255) field to a number field, either decimal or int, doesn't matter.

I have tried using the cast and convert but keep getting an error message that is cannot change the field. Here's the formula I am using.

CAST((CONVERT(VARCHAR(10), pnd.text)) AS INT)as weight

Not sure why it is not working?

Thanks

 

[r937]

perhaps it's not working because the column contains non-numeric data?

try this --

select pnd.txt
  from pnd
 where isnumeric(pnd.text) = 0

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[Crystalguru]

Yes, you are correct, some of the columns do not contain numeric data.

With a where statement like this
where pnd.typecode = weight. There is only number values.

So, I tweaked my statement to the following:
CAST((CONVERT(VARCHAR(8), pnd.text)) AS decimal)as weight

And it works!

Wala.

Thanks.