Olga Real Olga Real - 2 months ago 6
MySQL Question

SQL Select a row as if it has no space on it

id | col1 | col2 |
_________________________
1 | Aceh | Denpasar
2 | Aceh | Banda Aceh
3 | Sumatera | Asahan


I want to select the row with
id
2
with
WHERE
criteria
BandaAceh
at
col2


My desired result is

id | col1 | col2 |
_________________________
2 | Aceh | Banda Aceh


I know it won't work with simple
SELECT...WHERE
statement as I need to remove the space on the value of col2.

How can i make it possible to get as a result the row with id
2
by just using
BandaAceh
on its
WHERE
statement?

NOTE: I want an exact search, we can't really use
LIKE
as we have a exact value to put on the
WHERE
clause, its just a matter of a space character that we need to eliminate on the criteria

Answer

Like this:

 select * from table where replace(col2,' ', '') = 'BandaAceh'