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.
Advertisement
Answer
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.