SQL Select query where column like string with and without spaces

Question

I have a SQL Select query in PHP where i need to lookup rows in a table where a column is like a string with and without spaces

for example, if my value in the database is ABC 123 and i search for ABC123 the query will be

$sql = SELECT * from table where col_name LIKE '%ABC123%' ";

but i need it to return the row where col_name equals ABC 123

and vice versa, for example, if the database value is 123ABC and i search for 123 ABC the query will be

$sql = SELECT * from table where col_name LIKE '%123 ABC%' ";

and i need it to return the row where col_name equals 123ABC

VJ Hil · Accepted Answer · 2014-06-05 17:15:44Z

6

$sql = SELECT * from table where REPLACE(col_name,' ','') LIKE '%ABC123%' ";

answered Jun 5, 2014 at 17:15

VJ Hil

9046 silver badges15 bronze badges

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

4 Comments

how about the vice versa ?

select * from table where replace(col_name,' ','') = replace('ABC 123',' ','')

$sql = SELECT * from table where REPLACE(col_name,' ','') LIKE '%123 ABC%' ";

@user2710234:Its just the same .It`s all about the replace function