mySQL regex_replace remove all beginning numbers from mixed input-CodePudding

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.

CodePudding user response：

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.