Mysql query find replaced column value

Question

Hi anyone have idea for finding MySQL row like below code, but this one not working.

I have column value "1205_15_09" and I want to find this row using this condition like 120515

SELECT * replace(column_name , '_', '' ) AS new_name 
FROM `table_name` WHERE new_name LIKE '120515%';

Answer 1

You can use the following:

SELECT *, REPLACE(column_name, '_', '') AS new_value
FROM table_name 
WHERE REPLACE(column_name, '_', '') LIKE '120515%';

demo on dbfiddle.uk

You can't use the column alias on the WHERE part of your query because the SELECT part is executed after the WHERE part. See the following StackOverflow answer describing the query execution order of MySQL.

So you have to use the REPLACE on the WHERE part. In case you don't need to see / use the new_value you can remove the REPLACE on SELECT.

---

In case the format is always xxxx_xx_xx, you can also use a solution like the following:

SELECT *
FROM table_name 
WHERE column_name LIKE CONCAT(INSERT(INSERT('120515', 5, 0, '_'), 8, 0, '_'), '%');

demo on dbfiddle.uk

As @Madhur Bhaiya already mentioned in the comments, you can use a index on the column_name to improve the performance on a large table.

Answer 2

There is also a way with a HAVING clause. So there is not need to duplicate the REPLACE(...).

SELECT *, REPLACE(column_name, '_', '') AS new_value
FROM table_name 
HAVING new_value LIKE '120515%';