# Written by The Suhu (2021).
# Tested on MySQL 5.7.24, MySQL 8.0.23 and Oracle 12c
As an illustration I want to get data like this from a table.
Original:
'0080360401100
006901066632507'
7131196x282
72990340195455Â
0495133377 IDR
BSM 8867285031
0555 08 018817 50 4
143 0606267469
01687779830IDR
Expected:
0080360401100
006901066632507
7131196282
72990340195455
0495133377
8867285031
055508018817504
1430606267469
01687779830
Here's how to get it in MySQL and Oracle.
Previously I've tried using REGEXP
or CAST
to UNSIGNED
in MySQL 5.7
, but the result is not what I expected. To generate what I expect is to make a function
to check one by one from text/string whether a digit or not. The result of this function
is still string data type, so the leading zero number is not lost. This is the function
with the name extract_digits
created in MySQL 5.7
database:
DELIMITER $$
CREATE FUNCTION extract_digits (string_mixed VARCHAR(100)) RETURNS VARCHAR(100) NO SQL
BEGIN
DECLARE find_digit_position VARCHAR(100);
DECLARE string_digits VARCHAR(100) DEFAULT '';
DECLARE search_char VARCHAR(1);
DECLARE i INTEGER DEFAULT 1;
IF LENGTH(string_mixed) > 0 THEN
WHILE(i <= LENGTH(string_mixed)) DO
SET search_char = SUBSTRING(string_mixed, i, 1);
SET find_digit_position = FIND_IN_SET(search_char, '0,1,2,3,4,5,6,7,8,9');
IF find_digit_position > 0 THEN
SET string_digits = CONCAT(string_digits, search_char);
END IF;
SET i = i + 1;
END WHILE;
RETURN string_digits;
ELSE
RETURN '';
END IF;
END$$
DELIMITER ;
This is an example of its use in SELECT
:
SELECT text_field, extract_digits(text_field) AS digit_only FROM some_table
Update!
In MySQL 8.0.4
or above, there is a built-in function
with the name REGEXP_REPLACE
. the result is exactly the same as the previous function
above. this is an example of using REGEXP_REPLACE
in SELECT
:
SELECT text_field, REGEXP_REPLACE(text_field, '[^[:digit:]]', '') AS digit_only FROM some_table
In Oracle database, it's easier. like MySQL 8.0.4
or above version, there is already a built-in function from Oracle with the name REGEXP_REPLACE
. This is an example of its use in SELECT
:
SELECT text_field, REGEXP_REPLACE(text_field, '[^[:digit:]]', '') AS digit_only FROM some_table
That's all and if you find it useful please star (:star:) & share.