Wanted to share an sql function I end up using quite frequently when dealing with user input data – normalization to alphanumeric. It can also be modified into a function to extract numeric values. If you do modify it to that, make sure you keep returns varchar for the cases of extracting numeric values beginning with 0. You can also add other characters, such as the minus sign, if required.
Example use cases:
Matching producer codes:
|
1 2 3 4 |
select producer_id, lcase(alphanumeric(producer_id)) from table >'BLA-bla-541' ; 'blabla541' >'bla Bla+/541'; 'blabla541' |
Normalizing/matching phone numbers:
|
1 2 3 4 |
select phone_number, alphanumeric(replace(phone_number, '+', '00') from table >'+45 097 . 4521/4761' ; '004509745214761' >'0045/(0)97.4521.4761'; '004509745214761' |
function:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
DELIMITER ! DROP FUNCTION IF EXISTS alphanumeric! CREATE FUNCTION alphanumeric ( str VARCHAR(255) ) RETURNS VARCHAR(255) DETERMINISTIC BEGIN DECLARE i, len SMALLINT DEFAULT 1; DECLARE ret VARCHAR(255) DEFAULT ''; DECLARE c VARCHAR(1); SET len = CHAR_LENGTH( str ); REPEAT BEGIN SET c = MID( str, i, 1 ); IF c REGEXP '[[:alpha:]]' or c REGEXP '[[:digit:]]' THEN -- Change matching here SET ret=CONCAT(ret,c); END IF; SET i = i + 1; END; UNTIL i > len END REPEAT; RETURN ret; END ! DELIMITER ; |