mysql search for records using string replace

Question

Let's suppose I have a table like this...

id | value
----------
1  | 4
2  | a8
3  | 13
4  | a2
5  | 7

So some numbers in the value have an "a" in front of them and others don't.

Now let's say I wanted to pull out anything higher than 6 - including any number with an "a" in front of it.

So a query that looked something like this (inserting some PHP that I know wouldn't work in SQL)

SELECT * FROM table WHERE str_replace("a","",value)>6;

The expected results should be...

id | value
----------
2  | a8
3  | 13
5  | 7

Please note that I don't want to actually get rid of the "a"s in the SQL table. The table should remain untouched. It's okay if the result back doesn't have the "a" in it, though.

Mosty Mostacho · Accepted Answer · 2013-10-04 19:14:19Z

1

Basically you have to:

Clean the data
Cast to int (using + 0 as a shortcut)
Use the result to filter

Try this:

SELECT id, value FROM table
WHERE REPLACE(value, "a", "") + 0 > 6

If you also wanted to have the "cleaned" field in your results you would have to add the REPLACE into your select statement too:

SELECT id, REPLACE(value, "a", "") + 0 value FROM table
WHERE REPLACE(value, "a", "") + 0 > 6

answered Oct 4, 2013 at 19:14

Mosty Mostacho

43.6k16 gold badges99 silver badges124 bronze badges

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

1 Comment

rgbflawed Over a year ago

This is it! Thanks so much! (will mark as answer as soon as I'm allowed)

Collectives™ on Stack Overflow

mysql search for records using string replace

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related