Skip to content

Combine two columns and perform a where operation on the result

I have two columns, one for ‘firstName’ and one for ‘lastName’. I’m trying to perform a where operation on the concatenation of those two columns to match for a full name.

Here is what I’ve tried:

select * from table where concat_ws(' ', 'firstName', 'lastName') like '%some_value%'

This just returns a blank array.

Example Data:

firstName lastName
Clayton Smith
Barbara Clayman
Sam Peterson

when some_value = Clay, it should return ‘Clayton Smith’ and ‘Barbara Clayman’

I got a lot of this syntax from some other stackoverflow answers which were marked as solutions to a similar problem, however when implementing this myself I couldn’t get it to work.

FWIW I’m going to be building these queries with knex.js so if there’s a knex.js specific solution feel free to answer with that.



The arguments to your CONCAT_WS call should be in double quotes. If they’re in single quotes, Postgres will interpret them as string literals. This should work:

SELECT * FROM table WHERE CONCAT_WS(' ', "firstName", "lastName") ILIKE '%some_value%'

I also recommend using ILIKE rather than LIKE – this will make the pattern matching for your search term be case insensitve.

User contributions licensed under: CC BY-SA
2 People found this is helpful