CODEWITHSUNDEEP
postgresql
Ryan
Ryan

Reputation: 18119

PostgreSQL ILIKE on concatinated columns not working

I'm trying to query using ILIKE on a user's name in PostgreSQL. There are columns for first_name and last_name, and I'd like the search term to match against the two concatenated, with a space between, so that a user may search for either, or a full name using one input. "John" "Doe" or "John Doe".

This always returns no results:

SELECT * FROM user_profiles WHERE first_name || ' ' || last_name ILIKE '%ryan%'

This always returns the one result I am expecting:

SELECT * FROM user_profiles WHERE first_name ILIKE '%ryan%'

Based on everything I've read, the first query should work as I am expecting, but it doesn't. No results and no errors. What am I missing here?

Upvotes: 1

Views: 1509

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270523

The first query would return no results if last_name were NULL (and similarly if first_name were NULL).

So, try this instead:

WHERE first_name || ' ' || COALESCE(last_name, '') ILIKE '%ryan%'

or:

WHERE CONCAT_WS(' ', first_name, last_name) ILIKE '%ryan%'

The concat_ws() function ignores arguments that are not NULL (except for the first argument).

Upvotes: 1

Related Questions