MySQL type conversion while ignoring nonnumeric characters

Question

How do I convert from varchar(500) to float in MySQL while ignoring non-numeric characters? My code currently looks like this

select distinct(customer_id), cast(val_amt as float) from(
select prsn_real_gid, vital_nam, vital_performed_date,
case when val_amt ~'^[0-9]+' then val_amt else null end as val_amt from my_table);

but I get the following error message

[Amazon](500310) Invalid operation: Invalid digit, Value 'X', Pos 2, Type: Double 
Details: 
 -----------------------------------------------
  error:  Invalid digit, Value 'X', Pos 2, Type: Double 
  code:      1207
  context:   1.XYXY04
  query:     4147
  location:  :0
  process:   query0_118_4147 [pid=0]
  -----------------------------------------------;
1 statement failed.

My data for example looks like this:

customer_id  |  val_amt
111  | 23.45
112  | 21
113  | x
114  | /
115  | 
116  | 23/24

As you can see I have decimals, integers, letters, symbols and nulls. I want to ignore everything that's not a decimal or integer.

You mean to get the output only numbers and decimal correct? What MySQL version are you using? Might be able to achieve that using REGEXP_REPLACE — FanoFN
– FanoFN, Commented Dec 10, 2019 at 1:41

Gordon Linoff · Accepted Answer · 2019-12-09 19:42:27Z

1

You can use regexp:

select customer_id, val_amt + 0
from my_table
where val_amt regexp '^[0-9]+[.]?[0-9]*';

answered Dec 9, 2019 at 19:42

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

brightman1309 Over a year ago

Replaced regex with ~ and it worked, but I still do not have val_amt as a float and it still gives me a similar error to the first when I try to cast as a float.

Gordon Linoff Over a year ago

@brightman1309 . . . MySQL supports regexp, not ~: dev.mysql.com/doc/refman/8.0/en/pattern-matching.html (and dbfiddle.uk/…).

Collectives™ on Stack Overflow

MySQL type conversion while ignoring nonnumeric characters

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related