Remove dots and hyphen on where clause MySQL

Question

I need remove dots and hyphens on a string on compare in Where clause.

This is my query now:

Select * from tbl_sometable where some_column in ('10000000000', '1999999999')

But, the some_column have values like this: '129.012.120-01' and I need filter the values too, like the values in a clause "in".

How I can do this? I using MySQL, I see an example using Translate, but not work in MySQL.

Thanks and best Regards.

There is a post here that looks like it would solve your problem: http://stackoverflow.com/questions/287105/mysql-strip-non-numeric-characters-to-compare — dub stylee, Apr 07 '14 at 17:41

score 3 · Accepted Answer · answered Apr 07 '14 at 17:41

3

WHERE
  REPLACE
    (
      REPLACE
       (
         some_column, "-", ""
       ), ".", ""
    ) in ('10000000000', '1999999999')

answered Apr 07 '14 at 17:41

Abhik Chakraborty

score 2 · Answer 2 · answered Apr 07 '14 at 17:41

2

WHERE REPLACE(REPLACE(some_column, '.', ''), '-', '') in ('10000000000', '1999999999')

answered Apr 07 '14 at 17:41

Linger

2 Answers2