Find and Replace multiple strings using a POSIX extended regular expression

Question

I am using Snowflake database and hope to find a single expression that will find and replace multiple items. The column in question has rows containing, Y, Yes, N, NO, and other irrelevant strings. So, in the example below Y and Yes are replaced with TRUE, N and No are replaced with FALSE and the other strings remain as they are. I have wasted TOO much time on this. Any help is MOST appreciated!

dog
Yes
No
Y
N
Zip

This works: REGEXP_REPLACE(REGEXP_REPLACE(VALUE,'^y(es)$','TRUE',1,0,'i'),'^n(o)$','FALSE',1,0,'i') but I hope to reduce it to a single expression because there are other needed replacements and I hope to avoid numerous functions in functions...

This does not work, but I think it shows the goal: REGEXP_REPLACE(VALUE,'(y(es)?)|(N(o)?)','$1TRUE$2FALSE',1,0,'i')

https://en.wikipedia.org/wiki/Regular_expression#POSIX_basic_and_extended

Easy: you can't do that with the `regexp_replace`. – Wiktor Stribiżew Nov 12 '19 at 21:19 — Wiktor Stribiżew, Nov 12 '19 at 21:19
ok, is there another solution? – Keith Nov 12 '19 at 21:21 — Keith, Nov 12 '19 at 21:21
You already have it. Use two replacements. – Wiktor Stribiżew Nov 12 '19 at 21:21 — Wiktor Stribiżew, Nov 12 '19 at 21:21

score 1 · Accepted Answer · answered Nov 12 '19 at 22:14

You don't describe your data too much, but as Wiktor Stribiżew says, you can only substitute one string per regexp (from many, but to one).

If there is a single word per column, you can always use DECODE as a conversion table:

DECODE(Lower(VALUE), 'y', 'TRUE', 'yes', 'TRUE', 'n', 'FALSE', 'no', 'FALSE', VALUE)

Find and Replace multiple strings using a POSIX extended regular expression

1 Answers1