Getting Digits from String in MySQL or Oracle

getting-digits-from-string-in-mysql-or-oracle.md

Getting Digits from String in MySQL or Oracle

# 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.

MySQL

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

Oracle

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.