CODEWITHSUNDEEP
sqlregexstringamazon-redshiftcharacter-trimming
user3615208
user3615208

Reputation: 45

SQL - Remove double semicolons at the end of field

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?

Upvotes: 1

Views: 503

Answers (2)

Rahul Gupta
Rahul Gupta

Reputation: 1802

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

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

Upvotes: 0

Marth
Marth

Reputation: 24812

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)

Upvotes: 2

Related Questions