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

Postgresql – Why the result is different for SQL string equality and for LIKE operator in Postresql?

I have a table with login names. Login names does not contain any special character just [a-zA-Z0-9] and of type varchar(50).

I would like to find a user by login name eg.:

CREATE TABLE users( login character varying(50) );
INSERT INTO users VALUES ('user1');
INSERT INTO users VALUES ('user2');

SELECT *
FROM users
WHERE ...;

What is the difference between login LIKE 'exactMatch', login LIKE 'exactMatch%' and login = 'exactMatch'

I would expect that for this two-row database the following conditions both return the same result:

login LIKE '%user1'
login LIKE 'user1%'
login = 'user1'
login LIKE 'user1'

In some cases, for more complicated login names the last two conditions fail.

Tags:

2

Answers


  1. 0

    Firstly, lets answer the question you asked:

    What is the difference between login LIKE ‘exactMatch’, login LIKE ‘exactMatch%’ and login = ‘exactMatch’

    login LIKE 'exactMatch' and ‘login = ‘exactMatch’` are equivalent.

    When you put a % or _ into the string, you get into LIKE‘s pattern matching features. When you have login LIKE 'exactMatch%', you will get logins that start with ‘exactMatch’ followed by any number of characters.

    Now, the problem I think you describe at the end is that you are finding that you have to use % in your LIKE clauses to get the matches you expect. I would guess that this is due to there actually being unexpected characters in your login’s or possibly an issue with character sets.

    You might find the problem by running a query that looks for non-printable characters and strips out whitespace from the logins:

    SELECT login FROM users
WHERE login <> trim(regexp_replace(login, '[^[:print:]]', 'x', 'g'));
    Login or Signup to reply.
  2. 0

    Several operators and wildcards can be used in SQL when working with strings to match patterns.
    Following is the main difference between the three conditins which you mentioned in your question.

    login LIKE ‘exactMatch’ and login = ‘exactMatch’
    Rows where the login column exactly matches ‘exactMatch’ will satisfy both of these conditions. Both of these require an exact, case-sensitive match for ‘exactMatch’.

    login LIKE ‘exactMatch%’
    This condition will match rows where the login column starts with ‘exactMatch’. A wildcard, % matches any string of characters that comes after ‘exactMatch’.

    The outcome of the second query of your question is a result set rather than a boolean. The result will be a list of rows, each with a single column that has a boolean value indicating whether or not the row’s login column begins with "user1." While this is an uncommon application of the SELECT statement, the SQL query is however valid.

    The COUNT function can be used if you want to count the number of rows that satisfy this condition, it will give you the count of rows where the login column starts with ‘user1’.

    SELECT COUNT(*)
FROM users
WHERE login LIKE 'user1%';
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search