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..
What happens to punctuation and white spaces, with the condition checking for alpha, I guess all non alpha will be stripped.
Hey lokus 🙂
Indeed! Easily fixed however, just change ‘[[:alpha:]]’ to ‘[^0-9]’:
mysql> select strip_digits(‘l337 73×7 1S g@Y’)//
+———————————-+
| strip_digits(‘l337 73×7 1S g@Y’) |
+———————————-+
| l x S g@Y |
+———————————-+
1 row in set (0.00 sec)
I think there may be meaning in that equation!