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:
- What, if any, operator in postgres (like, rlike, similar to, …) most closely mimics the functionality/usability of the like operator in tsql?
- Is there a general rule of thumb when deciding which operator to use in postgress?
Thanks!
>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.