postgres - using SIMILAR TO in a CASE clause

Question

I have a column which contains the string of a scanned barcode. I want to find a specific match and return in a new column the string 'match' and 'noMatch'

The regex for the barcode is

'[0-9]{5,8}\%[0-9]*\%'

e.g. 13412432%10000%

My query is

SELECT 
report."barcode" SIMILAR TO '[0-9]{5,8}\%[0-9]*\%',
(CASE report."barcode" WHEN (report."barcode" SIMILAR TO '[0-9]{5,8}\%[0-9]*\%') THEN 'match'
ELSE 'noMatch'
END) AS matchColumn
FROM report

However I'm always getting this error

ERROR: operator does not exist: text = boolean LINE 3: (CASE report."barcode" WHEN (report."barcode...

HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. SQL state: 42883 Character: 106

I'm fairly new to SQL so when the docs says that it expects a true/false statement after WHEN I figured I could use the SIMIAR TO pattern matching since it returns boolean.

score 5 · Accepted Answer · answered Dec 29 '18 at 00:53

I have it on good authority (https://stackoverflow.com/a/12459689/1278553) that there is no good reason to use "similar to." From the referenced link:

SIMILAR TO is part of the SQL standard, but it is very odd syntax, and the only reason PostgreSQL supports it, is to stay standard compliant. Internally, every SIMILAR TO expression is rewritten with a regular expression. Therefore, for any given SIMILAR TO expression, there is at least one regexp doing the same job faster.

On that note, if you change this to a normal regular expression it should even be as simple as this:

select
  r.barcode, 
  case
    when r.barcode ~ '\d{5,8}%\d+%' then 'match'
    else 'noMatch'
  end as matchcolumn
from report r

You don't need to escape the % character with a regex.

If you aren't okay with that pattern occuring in the middle of a string, you may want to anchor the beginning and/or end of the regex:

'^\d{5,8}%\d+%$'

score 2 · Answer 2 · answered Dec 28 '18 at 21:51

You have an extra reference to the column after the case. The expression you want is:

SELECT report."barcode" SIMILAR TO '[0-9]{5,8}\%[0-9]*\%',
       (CASE WHEN report."barcode" SIMILAR TO '[0-9]{5,8}\%[0-9]*\%' 
             THEN 'match'
             ELSE 'noMatch'
        END) AS matchColumn
FROM report;

score 0 · Answer 3 · answered Dec 28 '18 at 21:56

0

you just need to omit report."barcode" after case

          Case --omit this report."barcode
              when  ......

answered Dec 28 '18 at 21:56

Himanshu

3,830
2
10
29

postgres - using SIMILAR TO in a CASE clause

3 Answers3