What is the best way to compare a name field with a string value?

I have a query that compares a string value against a name field in my database. The structure of the name field in the DB is not consistent, it could be any of these:

John Doe
Doe John
Doe, John

My string value can be in any structure, but right now it is like so:

Doe, John

Query:

full_name ILIKE 'Doe, John%'

This doesn’t always return records. What is the most reliable query to make a comparison in any structure?

Answer

Option 1 – as commented by melpomene : the IN condition

full_name IN ('John Doe', 'Doe John', 'Doe, John')

If you know in advance all the possible variations of the full name, this is the most efficient approach. With an index on column full_name, that should be very fast.

Option 2 : ensure that both first and last name are part of the string :

full_name LIKE '%John%' AND full_name LIKE '%Doe%'

This is really less efficient. Using LIKE with a wildcard on the left basically defeats an index on column full_name, and generates a full table scan.