Converting varchar to numeric return error

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 Development (2000)

Converting varchar to numeric return error

Author

Topic

zang
Starting Member

14 Posts

Posted - 2008-09-24 : 15:24:49

Error in select sentence converting data varchar to numeric

Description:
select cast(BarCode as numeric) as BC from Products;
This query return error State:37000, Native Code: 1FB2

Detailed example:
CREATE TABLE Products
(
ProductCode CHAR(15) NOT NULL, BarCode CHAR(20)
CONSTRAINT KF_ART_Cla_Articulo PRIMARY KEY (ProductCode)
);

insert into Products (ProductCode, BarCode ) values ('1', '7795465465');
insert into Products (ProductCode, BarCode ) values ('2', '779654-RED');
insert into Products (ProductCode, BarCode ) values ('3', '7796542354');

Goal:
I would like replace BarCode by 0 in the query when there is any character detected

In the sencod row (showed in the example) Barcode = ‘779654-RED’, ‘RED’ is non numeric data.
So my goal is to replace ‘779654-RED’ by 0

I Tried to do this using select cast(BarCode as numeric) as BC from Products;
But this doesn’t work, the error appears.

Any advice ?
Maybe someone has some experience on that.

Tanks in advance

Ariel

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-24 : 15:28:45

UPDATE Products
SET BarCode = '0'
WHERE BarCode LIKE '%[^0-9]%'

E 12°55'05.63"
N 56°04'39.26"

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-09-25 : 11:00:41

If you don't want to update the table:

select case when isnumeric(Barcode)=1 then cast(Barcode as numeric) else 0 end as Barcode from Products

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-25 : 11:08:51

quote:
Originally posted by hanbingl

If you don't want to update the table:

select case when isnumeric(Barcode)=1 then cast(Barcode as numeric) else 0 end as Barcode from Products

Isnumeric is not reliable

http://aspfaq.com/show.asp?id=2390

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources