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, rlike, 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?

Thanks!

CodePudding user response：

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.