RTRIM a pattern, not all the characters

Question

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!

I just checked [TechOnTheNet ](https://www.techonthenet.com/oracle/functions/rtrim.php) to see if it was incorrect based on what you said, but in fact it says "The RTRIM function may appear to remove patterns, but this is **not** the case..." — Tony Andrews, Jun 11 '18 at 14:14

Tim Biegeleisen · Accepted Answer · 2018-06-11T16:21:00.940

1

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');

edited Jun 11 '18 at 16:21

answered Jun 11 '18 at 14:05

Tim Biegeleisen

502,043
27
286
360

score 0 · Answer 2 · answered Jun 11 '18 at 14:15

0

You could use TRIM(TRAILING ... FROM):

SELECT col_name,
 REPLACE(TRIM(TRAILING '^' FROM REPLACE(col_name, 'NO', '^')), '^', 'NO') AS res
FROM tab;

DBFiddle Demo

answered Jun 11 '18 at 14:15

Lukasz Szozda

162,964
23
234
275

score 0 · Answer 3 · answered Jun 11 '18 at 14:17

Have a look at this, maybe?

declare @string varchar(150) = 'BROOKLYNNO'
select LEN(@string)
select LEFT(@string,(LEN(@string)-2))

You can then update your column with the output from the final select statement, which trims the last two letters from the string.

I suppose it might be worth asking how you're getting the data that you have here, strings appended with "NO"?

RTRIM a pattern, not all the characters

3 Answers3