0

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%';
vijin selvaraj
  • 124
  • 1
  • 6
  • 1
    Is the data in the column, always going to be in this format: `xxxx_xx_xx` ? If yes, then it would be better to modify the input value to this format, so that you can utilize index on the column. using `Replace()` function on the column, will not use any index at all, and will be extremely inefficient for large tables. – Madhur Bhaiya Aug 22 '19 at 07:01

2 Answers2

2

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.

Sebastian Brosch
  • 42,106
  • 15
  • 72
  • 87
1

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%';
Tuckbros
  • 417
  • 3
  • 13