mySQL regex_replace remove all beginning numbers from mixed input

Question

I need a regex pattern to remove all numeric input from the beginning of a field that stops at the first alphabetic character and leaves any remaining numbers that follow alphabetic characters.

Here is the simplest test case.

CREATE TABLE tempdemo (tdfields varchar(10));
INSERT INTO tempdemo (tdfields) VALUES ('1'), ('11'), ('111'), ('a'), ('ab'), ('abc'), ('1a'), ('a1');

SELECT REGEXP_REPLACE(tdfields, '^[0-9]*$', '') FROM tempdemo;

From the code, I would like the following output: :blank:, :blank:, :blank:, a, ab, abc, a, a1

Right now, the regex pattern leaves '1a' instead of 'a' which is undesireable.

Any help would be greatly appreciated. Thanks.

Simply remove EOL anchor: SELECT REGEXP_REPLACE(tdfields, '^[0-9]*', '') FROM tempdemo;. Now the pattern tells to find the digits group from the value beginning till the first non-digit (this can be EOL, letteror any other char) and remove this group. Ungreedy flag is not specified, so all digits until non-digit matches. — Akina
– Akina, Commented Aug 25, 2022 at 4:38
Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking. — Community
– Community Bot, Commented Aug 25, 2022 at 5:41

Wiktor Stribiżew · Accepted Answer · 2022-08-25 12:09:13Z

0

You can use

CREATE TABLE tempdemo (tdfields varchar(10));
INSERT INTO tempdemo (tdfields) VALUES ('1'), ('11'), ('111'), ('a'), ('ab'), ('abc'), ('1a'), ('a1');

SELECT REGEXP_REPLACE(tdfields, '^[0-9]+', '') FROM tempdemo;

It will remove one or more digits from the start of the string.

More details:

^ - start of string
[0-9]+ - one or more digits.

$ matches the start of string position.

Note that ^[0-9]* is not a good pattern here since it matches any string, and + variation will only match when necessary, when there are digits at the start of the string.

answered Aug 25, 2022 at 12:09

Wiktor Stribiżew

631k41 gold badges502 silver badges632 bronze badges

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

Collectives™ on Stack Overflow

mySQL regex_replace remove all beginning numbers from mixed input

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related