Mark Leith

We had a question in #mysql on freenode yesterday, asking if there was a function to strip digits from a string. The answer is of course – not natively.

I’ve been playing around with Functions and Stored Procedures a bit lately though, trying to familiarise myself with the MySQL implementation fully, and wrote this quick function which does the job, although only in 5.0:

DELIMITER //

DROP FUNCTION strip_digits //

CREATE FUNCTION strip_digits (str VARCHAR(50))
RETURNS VARCHAR(50)
BEGIN
DECLARE sub_start INT DEFAULT 0;
DECLARE res VARCHAR(50) DEFAULT '';

WHILE sub_start < LENGTH(str)+1 DO

IF SUBSTRING(str,sub_start,1) REGEXP '[[:alpha:]]' THEN
SET res = CONCAT(res,SUBSTRING(str,sub_start,1));
END IF;

SET sub_start = sub_start + 1;

END WHILE;
RETURN res;
END;
//


Here’s a couple of examples of it in action:

mysql> select strip_digits('foop123')//
+-------------------------+
| strip_digits('foop123') |
+-------------------------+
| foop |
+-------------------------+
1 row in set (0.00 sec)

mysql> select strip_digits('foop123a')//
+--------------------------+
| strip_digits('foop123a') |
+--------------------------+
| foopa |
+--------------------------+
1 row in set (0.00 sec)

mysql> select strip_digits('foop123ahgh63263jsdj')//
+--------------------------------------+
| strip_digits('foop123ahgh63263jsdj') |
+--------------------------------------+
| foopahghjsdj |
+--------------------------------------+
1 row in set (0.00 sec)

It even performs fairly well!

More to come in the “MySQL 5.0″ collection of blogs on Functions and Stored Procedures when I get time to sit down and do it justice! Even though I’ll never win a mug any more.. Hohum..