SQL error: Invalid operation: Invalid input syntax for type numeric

1

I wanted to extract the dosage from a long string which was stored in a column 'concept_name', and convert the digits of dosage into numeric type. Here is my code:

alter table scratch_tzhang83_226.ppi_exposure_1014 add column spe text;
update scratch_tzhang83_226.ppi_exposure_1014 set spe = split_part(substring(concept_name from strpos(upper(concept_name),'OMEPRAZOLE')+11),' ',1) where drug_type='Omeprazole';
update scratch_tzhang83_226.ppi_exposure_1014 set spe = split_part(substring(concept_name from strpos(upper(concept_name),'RABEPRAZOLE SODIUM')+19),' ',1) where drug_type='Rabeprazole';
select spe,count(*) from scratch_tzhang83_226.ppi_exposure_1014 group by spe order by spe;
update scratch_tzhang83_226.ppi_exposure_1014 set spe = split_part(substring(spe from strpos(spe,'+')+1),'mg',1) where charindex('+',spe);
update scratch_tzhang83_226.ppi_exposure_1014 set spe = split_part(spe,'mg',1) where charindex('mg',spe);

alter table scratch_tzhang83_226.ppi_exposure_1014 add column specification numeric(20,4);
update scratch_tzhang83_226.ppi_exposure_1014 set specification = cast(spe as numeric(20,4));

And then I got this error:

An error occurred when executing the SQL command:
select cast(spe as numeric(10,3)) from scratch_tzhang83_226.ppi_exposure_1014 limit 5

Amazon Invalid operation: Invalid input syntax for type numeric
Details:

error: Invalid input syntax for type numeric
code: 8001
context: value: ""
query: 569354
location: cg_util.cpp:1048
process: query0_422 [pid=92738]
-----------------------------------------------;

Execution time: 1.44s
Statement 1 of 1 finished

1 statement failed.

Can someone tell me how I can solve this?

BTW, I used the same method in several different databases, and this error happened in only one database.

2

Seems like you're converting blank to numeric.
If you want to skip those records:

select cast(spe as numeric(10,3))
  from scratch_tzhang83_226.ppi_exposure_1014
 where spe<>''
 limit 5

If you want to convert blanks to 0:

select cast(case when spe='' then '0' else spe end as numeric(10,3))
  from scratch_tzhang83_226.ppi_exposure_1014
 limit 5

Your database engine seems like it's postgresql (this is Microsoft SQL Server forum), so you might get better answers on a postgresql forum.

3

Thank you very much. :clap: