Need to removed

[JustATheory]

Greetings,
I have a column that displays weight, I need to strip out "lb" and convert it to a numeric or decimal (3,1). the column needs to be averaged, all is fine until the conversion, and I keep getting an error.


Msg 8115, Level 16, State 8, Line 96
Arithmetic overflow error converting nvarchar to data type numeric.[/highlight]

Can this be done in one statement? I have 10 columns with similar requirements. Please help!

Thanks you,

Weight
- -
230.2lb
220.2lb

SELECT

CAST(case when Weight = '- -' then NULL else REPLACE(Weight, 'lb', '') END as NUMERIC(3,1)) AS TEST

 

[pjw001]

I don't have an answer but to analyse the problem you could try running the sql without doing the cast and see what pops out. This might give you a clue as to what the problem is.

 

[mikrom]

I tried this small example:

create or replace table weight_tab (
   weight nvarchar (10)
)
;

insert into weight_tab values
  ('- -'), 
  ('10 lb'), ('20 lb'), 
  ('- -')
;

Then for me both of these selects work

select   
  case 
    when weight = '- -' then null
    else cast(replace(weight, 'lb', '') as numeric(3, 1))
  end as text
from weight_tab
;

select
  cast(
    case 
      when Weight = '- -' then null 
      else replace(Weight, 'lb', '') 
    end as numeric(3, 1)) as text
from weight_tab
;

and deliver same result:

TEXT                           
    -                          
10.0                           
20.0                           
    -                          
********  End of data  ********

 

[fredericofonseca]

220.2lb -- that should be numeric (4, 1)

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886