Mysql: replace leading zeros inside text
I want to replace about 3,500,000 values in a Mysql table. Each value is a string in the form AB00123012
and I want to remove the leading zeros after the letters - that is, get AB123012
(the zero number inside must be stored). The value always has exactly 10 characters.
Since Mysql doesn't allow regular expression substitution, I used the following function:
DELIMITER $$
CREATE FUNCTION fn_RawRingNumber (rn CHAR(10))
RETURNS CHAR(10) DETERMINISTIC
BEGIN
DECLARE newrn CHAR(10);
DECLARE pos INT(8);
DECLARE letters CHAR(2);
DECLARE nr CHAR(8);
IF (CHAR_LENGTH(rn) = 10) THEN
SET pos = (SELECT POSITION('0' IN rn));
SET letters = (SELECT SUBSTRING_INDEX(rn, '0', 1));
SET nr = (SELECT TRIM(LEADING '0' FROM SUBSTRING(rn,pos)));
SET newrn = (SELECT CONCAT(letters, nr));
ELSE
SET newrn = rn;
END IF;
RETURN newrn;
END$$
DELIMITER ;
While this is working it is rather slow and I am wondering if there is no better way to do this?
source to share
If you can afford to host your site offline within minutes, the fastest way is to dump, process, and re-import. Since the current operation is making queries / inserts on this table rather slow, so dump / process / import is probably your best bet.
Dump stage 1.
SELECT INTO OUTFILE is your friend here
Step 2 process
Use your favorite programming language or if you are lucky enough to be on Linux, something like sed
or even cut
. If you need help with the regex post please post a comment.
Step 3 reimport
After clearing the table. Do the DOWNLOAD DATA POSITION.
these three steps should be fast enough. Especially if you have index n on that column.
source to share
try it
Note. I have not tested this with many lines and am not sure how efficient it is.
Also, if it's fast, please consider before using this, all the possible choices that might come up with your string might be missing some choices, not 100%.
select case
when INSTR(col, '0') = 2 then concat( substr(col, 1, 1), substr(col, 2) * 1)
when INSTR(col, '0') = 3 and substr(col, 2, 1) not in('1','2','3','4','5','6','7','8','9') then concat( substr(col, 1, 2), substr(col, 3) * 1)
else col
end
from (
select 'AB00123012' as col union all
select 'A010000123' as col union all
select 'A1000000124' as col union all
select 'A0000000124' as col union all
select '.E00086425' as col
) t
source to share