RTRIM a pattern, not all the characters

rtrim oracle
rtrim php
oracle rtrim last character
rtrim sql
oracle remove last 3 characters from string
rtrim mysql
rtrim excel
ltrim and rtrim in oracle with example

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!


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

Oracle / PLSQL: RTRIM Function, The Oracle / PLSQL RTRIM function removes all specified characters from the This website would not exist without the advertisements we display and your kind The RTRIM function may appear to remove patterns, but this is not the case  RTRIM( string1 [, trim_string ] ) Parameters or Arguments string1 The string to trim the characters from the right-hand side. trim_string Optional. The string that will be removed from the right-hand side of string1. If this parameter is omitted, the RTRIM function will remove all trailing spaces from string1.


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

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

DBFiddle Demo

rtrim - Manual, PHP 5, PHP 7). rtrim — Strip whitespace (or other characters) from the end of a string Simply list all characters that you want to be stripped. isn't matched. Returns a character string after truncating all trailing spaces. Transact-SQL Syntax Conventions. Syntax RTRIM ( character_expression ) Arguments. character_expression Is an expression of character data. character_expression can be a constant, variable, or column of either character or binary data.


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"?

PostgreSQL TRIM Function, The RTRIM() function removes all characters, spaces by default, from the end of a string. TRIM() function only allows you to remove leading and trailing spaces, not all other whitespace characters. The \s+$ pattern is explained as follows:. The LTRIM () function removes all characters, spaces by default, from the beginning of a string. The RTRIM () function removes all characters, spaces by default, from the end of a string. The BTRIM function is the combination of the LTRIM () and RTRIM () functions.


Remove leading and trailing whitespace in Java (left trim and right , In ASCII, whitespace characters are space (' '), tab ('\t'), carriage return ('\r'), newline ('\n'), vertical private final static Pattern RTRIM = Pattern.compile("\\s+$​");. The Oracle  TRIM function will remove characters from the start or the end of a supplied string. It’s often used to remove space characters, such as where users enter an extra space that is not needed. The Oracle  LTRIM function will remove a specified character from the left side of a string.


Special pattern matching character operators, The simplest and very common pattern matching character operators is the . This simply allows for Note that \w matches a single alphanumeric character, not a whole word. To match a is used, \ matches the digit'th substring. Outside of the​  This function returns a string with whitespace (or other characters) stripped from the end of str . Without the second parameter, rtrim () will strip these characters: "\x0B" (ASCII 11 (0x0B)), a vertical tab. You can also specify the characters you want to strip, by means of the character_mask parameter. Simply list all characters that you


DBMS – Complete Practical Approach, Remember: RPAD does not return the rightmost five characters (in the above RPAD does, in fact, generate a repetition of the pattern specified in the pad string. VARCHAR2 RTRIM returns stringl with all trailing characters removed up to  An array of characters is passed to the TrimStart method to specify the characters to be removed. As with the TrimEnd method, the order of the elements in the character array does not affect the trim operation. The trim stops when a character not specified in the array is found. The following example removes the first word of a string.