Search code examples
sqloracle-databasetrimcharacter-trimming

RTRIM a pattern, not all the characters

I have strings like these:

JAPANNO
CHINANO
BROOKLYNNO

I want to delete the 'NO' from all of the strings. I tried this:

rtrim(string, 'NO')

but for example in the case of BROOKLYNNO, I got this:

BROOKLY.

It deletes all the N-s from the end. How can I delete just the pattern of 'NO'? I know I can do it with substr, but the TechOnTheNet says there is a way to delete a pattern with RTRIM, and I really want to know the way.

Thank you in advance!

Solution

  • We may consider doing a regex replacement via REGEXP_REPLACE, if you give a context for when NO should be removed and when it should not. For example, if you wanted to remove NO from the ends of your strings only, we could do the following:

    UPDATE yourTable
SET col = REGEXP_REPLACE(col, 'no$', '', 1, 0, 'i');