MYSQL REGEXP_REPLACE after numbers

Question

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

To remove anything after last digit: REGEXP_REPLACE(mystring, '(.*\\d).*', '$1') — bobble bubble
– bobble bubble, Commented Jun 30, 2022 at 10:22
You have recently obtained voting privileges, kindly upvote the answers (see how) that you like. — Ryszard Czech
– Ryszard Czech, Commented Jul 7, 2022 at 20:39

Wiktor Stribiżew · Accepted Answer · 2022-06-30 09:54:53Z

3

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.

answered Jun 30, 2022 at 9:54

Wiktor Stribiżew

631k41 gold badges502 silver badges632 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

wanna Over a year ago

i have tried the answer and it works! tqvm

Tim Biegeleisen · Accepted Answer · 2022-06-30 09:55:23Z

1

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.

answered Jun 30, 2022 at 9:55

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Collectives™ on Stack Overflow

MYSQL REGEXP_REPLACE after numbers

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related