I need to get similarity between two texts abc_0 and abc_[0-9]. I am able to find similarity when I do: select 'abc_0' ~ 'abc_[0-9]' returns true but select 'abc_[0-9]' ~ 'abc_0' returns false. I need this to return true.

  • why do you need that operand order? – Jasen Jul 31 '18 at 9:27
  • Hey Jasen ! The left hand side will be my input text from UI. e.g in select 'abc_[0-9]' ~ 'abc_0', abc_[0-9] will be my input from UI. So suppose I have saved 'abc_0.csv' in DB, I should not allow abc_[0-9].csv because its similar to abc_0.csv – anoop_singh05 Jul 31 '18 at 9:32
  • select 'found' from TABLENAME where column ~ 'USERINPUT' or 'USERINPUT' ~ column -- but likely to give error more often than results, also 'abc_[0-9]' ~ 'abc_[0-9]' is false. – Jasen Jul 31 '18 at 10:27
  • Note that the ~ operator explicitly compares the left hand input to a pattern defined in the right-hand input. If you're trying to see if the literal string "abc_[0-9].csv" is similar to the literal string "abc_0.csv", you'd have to convert the second literal string to a pattern of some sort, and you'd have to thus define what qualifies as "similar". PostgreSQL has a SIMILAR operator, but it also compares your left value (a literal string) to your right value (a pattern). – RDFozz Jul 31 '18 at 15:09

select 'abc_[0-9]' ~ 'abc_0' returns false

Of course it does.

Does the string value consisting of the characters ...

{ 'a', 'b', 'c', '_', '[', '0', '-', '9', ']' } 

... match the Regular Expression "abc_0"? No, it doesn't.

Why do you need this? You cannot simply "invent" SQL syntax.

If there are files involved, would it not be better to check whether the file already exists or not, regardless of what it may or may not be called?

  • You can invent new operators. It would be easy to define one that does what he wants. There are some cases where it might even be useful, not just possible. – jjanes Jul 31 '18 at 14:29

Right syntax is

SELECT string_variable ~ regexp_mask

It returns true if and only if string_variable matches regexp_mask.

In first example

SELECT 'abc_0' ~ 'abc_[0-9]'

regexp_mask is 'abc_[0-9]'. Such example will check if string 'abc_0' contains text 'abc_' and then 1 digit symbol from interval [0-9].

In second example

SELECT 'abc_[0-9]' ~ 'abc_0'

regexp_mask is 'abc_0' It checks if string 'abc_[0-9]' contains text 'abc_0'. But it has a brace [ between abc_ and 0, that's why such check returns false.

In first example braces [] means interval, in second - text.

Not the answer you're looking for? Browse other questions tagged or ask your own question.