MYSQL REGEXP_REPLACE after numbers-CodePudding

I want to replace data after numbers with '' using regex_replace.

For example:

input --> output
MA0244891-D --> MA0244891
MA0244891 --> MA0244891
MA0244891D --> MA0244891
0244891D --> 0244891

I tried a few regex_replace as below:

REGEXP_REPLACE(rk.mystring, '[^0-9] ', '')) --> only get numbers
REGEXP_REPLACE(rk.mystring, '[^a-zA-Z0-9] ', '')) ---> get alphanumeric including the last characters
REGEXP_REPLACE(rk.mystring, '[^a-zA-Z][^0-9] ', '')) ---> almost correct but truncate numbers at the back

Appreciate your kind help

CodePudding user response：

You can remove any non-alphanumeric in the string and any letters at the end of string:

REGEXP_REPLACE(rk.mystring, '[^0-9A-Za-z]|[a-zA-Z] $', '')

See the regex demo.

Details:

[^0-9A-Za-z] - any char other than ASCII digits and letters
| - or
[a-zA-Z] $ - one or more ASCII letters at the end of string.

CodePudding user response：

You may replace on the pattern -?[A-Z]$:

SELECT REGEXP_REPLACE(mystring, '-?[A-D]$', '') AS mystring_out
FROM yourTable;

Here is a running SQL demo.