PostgreSQL similar to operator behaviour

Question

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.

There is no point in using SIMILAR TO. Ever. I use LIKE or regular expressions instead. stackoverflow.com/questions/12452395/… — Erwin Brandstetter
– Erwin Brandstetter, Commented Nov 4, 2017 at 6:40

Hambone · Accepted Answer · 2017-11-04 04:56:23Z

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'

answered Nov 4, 2017 at 4:56

Hambone

16.5k8 gold badges54 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL similar to operator behaviour

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related