PostgreSQL LIKE – Can LIKE Statement Return Exact Match?

likepostgresqlstring-searching

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');

Best Answer

% 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
Related Question