0

I find PostgreSQL similar to operator works little strange. I accidentally checked for space in below query but surprised with the result.

select 'Device Reprocessing' similar to '%( )%' --return true select 'Device Reprocessing' similar to '%()%' --return true select 'DeviceReprocessing' similar to '%()%' --return true

Why 2nd and the 3rd query returns true? Is empty pattern always return true?

What I understand about SIMILAR TO operator is returns true or false depending on whether its pattern matches the given string.

venkat
  • 503
  • 6
  • 15
  • 1
    There is no point in using `SIMILAR TO`. Ever. I use `LIKE` or regular expressions instead. https://stackoverflow.com/questions/12452395/difference-between-like-and-in-postgres/12459689#12459689 – Erwin Brandstetter Nov 04 '17 at 06:40

1 Answers1

2

You have defined a group with nothing in it, meaning anything will match. I think you will find any string matches %()%, even an empty string.

Normally you would use this grouping to list options so:

select 'DeviceReprocessing' similar to '%(Davinci|Dog)%'

Would return false since it contains neither "Davinci" nor "Dog", but this:

select 'DeviceReprocessing' similar to '%(vice|Dog)%'

would return true since it does contain at least one of the options.

Your first condition is true because the expression does contain a space.

I actually prefer the Regular Expression notation that does not require the % wildcards:

select 'DeviceReprocessing' ~ 'vice|Dog'
Hambone
  • 15,600
  • 8
  • 46
  • 69