Question 90091 in PostgreSQL Question posted in
The official documentation can be found here.

Filter full name postgresql

SELECT * 
FROM employee 
WHERE is_deleted != true 
  AND (:lastname IS NULL 
       OR lastname ILIKE '%'||lastname||'%'
       OR :firstname IS NULL
       OR firstname ILIKE '%'||:firstname||'%'
       OR :middlename IS NULL
       OR middlename ILIKE '%'||:middlename||'%');

I have a full name column and I need to filter by first name, last name or patronymic, depending on what the user enters (or last name and first name together) b tell me how to implement

Tags:

2

Answers


  1. 0

    Your logic is correct except the the various name criteria should be ANDed together:

    SELECT * 
FROM employee 
WHERE is_deleted != true AND
      (:lastname IS NULL OR lastname ILIKE '%' || lastname || '%') AND
      (:firstname IS NULL OR firstname ILIKE '%' || :firstname || '%') AND
      (:middlename IS NULL OR middlename ILIKE '%' || :middlename || '%');
    Login or Signup to reply.
  2. 0 
    SELECT *
FROM employee
WHERE is_deleted != true
  AND (:search_term IS NULL OR 
       (split_part(full_name, ' ', 1) ILIKE '%' || :search_term || '%' OR
        split_part(full_name, ' ', 2) ILIKE '%' || :search_term || '%' OR
        split_part(full_name, ' ', 3) ILIKE '%' || :search_term || '%'));

    This query will use the split_part() function to extract the first, second, and third parts of the full name, and then use them in the filtering. The ILIKE operator is used for case-insensitive matching. If the search_term parameter is null, the filtering will be skipped, and all the rows will be returned.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search