SQL regex replace for multiple conditions

Question

I am trying to cleanup some text. To achieve that, I came up with following query

Select message,
Case
when REGEXP_Count(message, 'Refreshing|update|create', 1, 'i') > 0 then REGEXP_REPLACE(message, 'Refreshing|Update|create', 'Edited',1, 1, 'i')
when REGEXP_Count(message, 'Open|View', 1, 'i') > 0 then REGEXP_REPLACE(message, 'Open|View', 'Viewed',1, 1, 'i')
else message 
end
From audit_log al

Is there a better way to handle such replacement so I do not have to repeat REGEXP function twice? I'll need to handle approx 50 different replacements

Tag your question with the database you are using.

Gordon Linoff
– Gordon Linoff

2021-05-12 17:56:23 +00:00
Commented May 12, 2021 at 17:56 — Gordon Linoff
– Gordon Linoff, Commented May 12, 2021 at 17:56

Gordon Linoff · Accepted Answer · 2021-05-12 17:58:34Z

1

You could just replace each value:

REGEXP_REPLACE(REGEXP_REPLACE(message, 'Refreshing|Update|create', 'Edited',1, 1, 'i'
                             ), 'Open|View', 'Viewed', 1, 1, 'i'
              )

This is not 100% equivalent. But assuming that the message has only one of the strings, it should do what you want,.

answered May 12, 2021 at 17:58

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Chris Maurer · Accepted Answer · 2021-05-12 19:09:18Z

0

With 50 or more of these mappings, it seems to me that you have crossed a threshold and you need a different kind of solution to help you manage and maintain this better.

Why don't you create a simple mapping table with two columns, action and actionclass, then populate it with ('refreshing','edited'), ('update','edited'), etc.

If there are more than one of these keywords in a message then both this solution and your solution behave strangely. This solution returns two instances of the same record with different keywords replaced, while your solution ignores the match of the second keyword entirely.

Select message, Regexp_Replace(message, m.action, m.actiontype,1,1,'i') as newmsg
From audit_log, Actions m
Where instr(message, m.action)> 0

answered May 12, 2021 at 19:09

Chris Maurer

2,9321 gold badge13 silver badges9 bronze badges

1 Comment

Macin Over a year ago

It has to be one query as I am not able to create any mapping tables. On the other hand this is just a one off job, so really not a problem when it comes to maintaining the list.

Collectives™ on Stack Overflow

SQL regex replace for multiple conditions

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related