Serach row where first name and last name matches against full name separated by space

Question

I have two columns first_name and last_name.

To search for a row against passed search text I am using iLik query.

SELECT * FROM t1 WHERE t.first_name ILIKE %John% or t.last_name ILIKE %John%;

Above works.

But I want to search for full name, both on first_name and last_name. It does not return the row.

SELECT * FROM t1 WHERE t.first_name ILIKE %John Doe% or t.last_name ILIKE %John Doe%;

Above empty result.

How can I make this to search on both the columns matching against sub parts of the search text?

Answer 1

I presume that John & Doe are in the two separate columns, so 'John' in t.first_name & 'Doe' in t.last_name. Your bottom query is looking for the full name in just one of the columns. If the names have been split out between the two columns then the full name will not appear in one column. I am pressuming that the columns will also just contain that first and last name, so the ILIKE will not be needed and you can just run an equals to,In order to achieve what you need, please run the following:

Equals version - SELECT * FROM t1 WHERE t.first_name = 'John' AND t.last_name = 'Doe'; Wildcard version - SELECT * FROM t1 WHERE t.first_name ILIKE %John% AND t.last_name ILIKE %Doe%;

Let me know how you get on.