I have a table that has a column “codes” as comma-separated values. Sample values below –
row 1 – 3fa, n38, iw9, 94a, l2v, 20z
row 2 – 92z, 29r, 02z, 99w, 00b, 92i
I want to find relevant rows, based on a boolean expression (with BODMAS rule)
If the input is (3fa+n38)-02z, the condition should translate to “find all records that have 3fa and n38, but not 02z”. So this will return the first row.
If the input is (((92z+00b)-20z)/02z) the condition should translate to “find all records that have 92z and 00b, but not 20z OR records that have 02z.
So, the operators translation is as below
- is as NOT CONTAINS
/ is OR
We have the flexibility of storing this codes column as either a comma separated value or as a string array. I am trying to build a Postgres function for it. I made it work via. dynamic SQL and REGEXP_REPLACE, but wanted to check if there was a more elegant way of doing it.
Leave an answer