0

I'm trying to search a Postgres table using LIKE and %, but the query is not returning results.

The column local_username will contains values formatted like: username@domain.local OR username@domain.

My query is searching this one column based on both the username and domain sections.

An example query is below:

SELECT * 
FROM users 
WHERE local_username LIKE 'username%' 
  AND local_username LIKE '%domain%';

I'm concerned that, because there may be no characters after domain in the query, the second % is going to cause it to not return any results, since the % causes it to search for more characters, which in the case of the above query are not present.

Is there another keyword other than LIKE that I should be using? Or do I need to check each possibility (example below).

SELECT * 
FROM users 
WHERE local_username LIKE 'username%' 
AND (local_username LIKE '%domain%' OR local_username LIKE '%domain');
1

% will match any sequence of characters, including none. So this is a match:

SELECT 'match' WHERE 'username@domain.com' like '%.com%';
-- Result: match

If you have the exact username and the exact domain you can use the following filter:

SELECT * 
FROM users 
WHERE 
    local_username = 'username@domain' OR   -- Exact match
    local_username LIKE 'username@domain.%';    -- Exact match until dot

If you have partial filters, you will have to add a few more % (place depends your needs):

SELECT * 
FROM users 
WHERE 
    local_username LIKE 'username%@domain' OR   -- Exact domain ending
    local_username LIKE 'username%@domain.%';   -- Exact domain ending until dot
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.