Extract value after the last number in a string-CodePudding

HAVE
M1234TESTABC
M34567TESTABC
M100023459ABC
M234TEST

WANT
TESTABC
TESTABC
ABC
TEST

CodePudding user response：

You can use REGEXP_SUBSTR:

SELECT REGEXP_SUBSTR(value, '\D $') as last_part
FROM   table_name;

Which, for the sample data:

CREATE TABLE table_name(value) AS
SELECT 'M1234TESTABC' FROM DUAL UNION ALL
SELECT 'M34567TESTABC' FROM DUAL UNION ALL
SELECT 'M100023459ABC' FROM DUAL UNION ALL
SELECT 'M234TEST' FROM DUAL;

Outputs:

LAST_PART
TESTABC
TESTABC
ABC
TEST

fiddle

CodePudding user response：

I would use a regex replacement approach here:

SELECT col, REGEXP_REPLACE(col, '.*[0-9]', '') AS col_out
FROM yourTable;

This approach behaves correctly should the input have no numbers at all, in which case the original value would be returned.