CODEWITHSUNDEEP
sqlregexpostgresqlpattern-matching
Han Che
Han Che

Reputation: 8519

postgres - using SIMILAR TO in a CASE clause

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.

Upvotes: 0

Views: 6066

Answers (3)

Hambone
Hambone

Reputation: 16377

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+%$'

Upvotes: 5

Himanshu
Himanshu

Reputation: 3970

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

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

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269713

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;

Upvotes: 2

Related Questions