sqlpostgresql

Like vs Similar to vs rLike

I am trying to understand the differences between like, rlike and similar to in postgres and whether there are specific use cases for each operator.

Being familiar with TSQL I generally used the like operator to do things like

SELECT * FROM table WHERE colA LIKE '%[0-9]%[a-z]%'

But in Postgres the same statement would not yield the same results.

My questions are:

  1. What, if any, operator in Postgres (like, ilike, similar to, ...) most closely mimics the functionality/usability of the like operator in tsql?
  2. Is there a general rule of thumb when deciding which operator to use in Postgress?
Solution

  • LIKE does not support regular expression in SQL (and SQL Server's implementation isn't a real regex to begin with).

    The similar to operator is probably the closest to SQL Server's LIKE as it supports the % wildcards from the "regular" LIKE operator but also allows to use a regex,

    So

    where cola similar to '%[0-9]%[a-z]%'

    should return the same as in SQL Server with the exception that it's case sensitive while SQL Server is not by default.

    If you want a real regex, then you need to use the ~ operator

    Select * 
From Table 
where ColA ~ '[0-9].*[a-z]'

    In a "real" regular expression the % is not a wildcard, so you need to use .* which matches zero or more characters. ~ automatically does a partial match so there is no need to "translate" the leading and trailing % from your expression.

    If you want to use a case insensitive match, use ~* instead.