SQL - Remove double semicolons at the end of field

Question

We are using Amazon-Redshift (PostgreSQL compliant syntax), we have the following string in a table

"TOTO;"
"TOTO;;"
"TOTO;;;"
"TOTO;;;;"

I would like to 'rtrim' double semicolons. So I would like to have

"TOTO;"
"TOTO"
"TOTO;"
"TOTO"

How to do it?

Answer 1

Use the regexp_replace function with the (;;)*$ regex (any number of ;; followed by the end of the line):

SELECT regexp_replace(';;test;;;', '(;;)*$'), regexp_replace(';;test;;;;', '(;;)*$');
┌────────────────┬────────────────┐
│ regexp_replace │ regexp_replace │
├────────────────┼────────────────┤
│ ;;test;        │ ;;test         │
└────────────────┴────────────────┘
(1 row)