Search code examples
mysqlsql-likeconcatenation

Mysql like '%search%' not found with concat colums

I create this table:

create table if not exists `example`(
`firstNames` varchar(45) not null,
`secondNames` varchar(45) not null)
ENGINE = InnoDB;

Now I insert one row:

insert into example values('Jose Alonzo', 'Pena Palma');

And a check if is correct

select * from example;

| firstNames | secondNames |
----------------------------
| Jose Alonzo|  Pena Palma |

Its ok! Easy Now I create a statment to search this row

set @search = 'jose alonzo pena';
select * from example
where concat(firstNames, ' ', secondNames) like concat('%',@search,'%');

This return

| firstNames | secondNames |
----------------------------
| Jose Alonzo|  Pena Palma |

Now I change the value @search for 'jose pena'

set @search = 'jose pena';
select * from example
where concat(firstNames, ' ', secondNames) like concat('%',@search,'%');

And do not return nothing!

| firstNames | secondNames |

What is happening? I can't use like for characters that are in the middle of the varchar?

Solution

  • No, you cannot use like for characters that are in the middle of the string. Or, in other words, a space character matches a space character, not an arbitrary string of characters. The following would match:

    where concat(firstNames, ' ', secondNames) like concat('%', replace(@search, ' ', '%'), '%')

    The order would be important, so this would match concat(firstNames, ' ', secondNames) but not concat(secondNames, ' ', firstNames).

    If you are interested in these types of searches, you should investigate full text indexes. In addition to being more powerful, they are also faster.