Hi,
In my DB, I have four columns of phone data which are poorly formatted (some entries contain text, other chars, multiple valid numbers etc.) and I am trying to extract valid phone numbers and store them in a fifth column in a clean format. The SQL query I have extracts and stores the phone numbers correctly on my MySQL (8.0) database but when I try it on a MariaDB (10.6) database it does not work.
Here’s the code I have so far:
UPDATE info -- table
SET phones = ( -- output column
SELECT NULLIF( -- set NULL if empty result
GROUP_CONCAT( -- combine phone numbers by comma
DISTINCT REGEXP_REPLACE( -- get unique values
REGEXP_SUBSTR(
REGEXP_REPLACE(source_column, '[^0-9]', ''), -- remove all non-numeric chars
'1?([0-9]{10})', 1, numbers.n -- get 10-digit numbers
),
'^1', '' -- remove leading 1
) ORDER BY col_pos, numbers.n SEPARATOR ',' -- preserve column order
), ''
)
FROM (
SELECT col1 AS source_column, 1 AS col_pos UNION ALL -- get values from all source columns
SELECT col2, 2 UNION ALL
SELECT col3, 3 UNION ALL
SELECT col4, 4
) AS columns
JOIN ( -- account for multiple phones in column entries
SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 -- this assumes a column entry will have max 10 multiple phone numbers
) AS numbers
ON REGEXP_SUBSTR(
REGEXP_REPLACE(columns.source_column, '[^0-9]', ''),
'1?([0-9]{10})', 1, numbers.n
) IS NOT NULL
);
I will appreciate any pointers to how to edit the code in order to make it work on MariaDB. Thanks!
