Oracle regexp - removing trailing underscore and numbers from string

I have values like this: aaa_1, bb_5, c_21, a_b. Now i need remove trailing underscore and numbers from this values. 开发者_StackOverflowA_b (string_string) is valid value and need not to be removed. Basicaly, i need regexp patern to extract only trailing _[any number of digits] from string.

I am using Oracle 11gR2

Any suggestion?

You need to use REGEXP_REPLACE with the regex expression '\_[[:digit:]]{1,}'

This translates to find the (escaped) underscore with one or more digits after it:

e.g.

SELECT REGEXP_REPLACE('a_b', '\_[[:digit:]]{1,}'),
       REGEXP_REPLACE('aaa_1', '\_[[:digit:]]{1,}'),
       REGEXP_REPLACE('c_21', '\_[[:digit:]]{1,}'),
       REGEXP_REPLACE('bb_5', '\_[[:digit:]]{1,}')
  FROM dual;

Returns

a_b
aaa
c
bb

If you want to make sure you only remove the underscore and digits when there are no alpha characters then add the $ to the end of the regex string to signify the end of the string.

e.g. First with the "$" second without

SELECT REGEXP_REPLACE('bb_5b', '\_[[:digit:]]{1,}$'),
       REGEXP_REPLACE('bb_5b', '\_[[:digit:]]{1,}'),
       REGEXP_REPLACE('bb_55', '\_[[:digit:]]{1,}$'),
       REGEXP_REPLACE('bb_55', '\_[[:digit:]]{1,}')
  FROM dual;

Returns

bb_5b
bbb
bb
bb

Regular expressions can be awkward but this link is one of the easiest to understand that I have found for Oracle regex expressions: http://www.dba-oracle.com/t_regular_expressions.htm

Hope this helps.

Here is another version, which will ignore any values that start with numbers if you have them. Same basic approach as Ollie, but takes into account the first part of the value to make sure they are letters. It captures both parts of the value and replaces it with the first part if it matches your criteria.

select
  regexp_replace('A_1', '([A-Za-z]+)(\_\d+)', '\1'),
  regexp_replace('A_B', '([A-Za-z]+)(\_\d+)', '\1'),
  regexp_replace('cc_21', '([A-Za-z]+)(\_\d+)', '\1'),
  regexp_replace('1_1', '([A-Za-z]+)(\_\d+)', '\1')
 from dual;