Search code examples
postgresqlsql-like

Issue with string comparison contains "_" in Postgres

Issue with comparison in Postgres (Version 11) while comparing _ sign.

I have a string (shown below) I want it to compare it with a word and wants to check whether this word _WIN_ exists in the string. If yes it should give true.

But when I search like this '%_WIN_%' it is giving as TRUE even though the searched string doesn't exactly contains this word.

Can anyone please suggest what I'm doing wrong?

select 'New_vit_Vitamin_D_IND-tonline_WINTERSEAS_2020.02.09' ILIKE '%_WIN_%'

Note: Expected RESULT should be FALSE but giving as TRUE

Solution

  • The underscore is the wildcard for a single character in SQL. If you want to search for the character itself you need to escape it:

    ILIKE '%\_WIN\_%' ESCAPE '\'