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?

Have you tried the trim operator? (https://www.postgresql.org/docs/9.1/static/functions-string.html) — TYZ, Sep 22 '17 at 15:15
if we do select trim('toto;;;', ';;'), it removes all ';' ... — user3615208, Sep 22 '17 at 15:17
This would work - `(?:;;)+` Demo: https://regex101.com/r/cEQbB5/1 — Gurmanjot Singh, Sep 22 '17 at 15:51
@Gurman: You'll need a `$` at the end to avoid removing every `;;` in the string. — Marth, Sep 22 '17 at 18:29

score 2 · Accepted Answer · answered Sep 22 '17 at 15:19

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)

score 0 · Answer 2 · answered Sep 22 '17 at 15:21

0

select replace('TODO;;;',';;','')

If I’ve made a bad assumption please comment and I’ll refocus my answer.

answered Sep 22 '17 at 15:21

Rahul Gupta

1,744
2
17
28

This will remove all `;;` occurences, not just the trailing ones. – Marth Sep 22 '17 at 15:23
@Marth - My bad! regexp_replace is the solution. – Rahul Gupta Sep 26 '17 at 20:43

SQL - Remove double semicolons at the end of field

2 Answers2