如果你经常使用MySQL,最好使用string functions SUBSTRING()和ASCII()制作Stored Function.
DELIMITER //;
CREATE FUNCTION find_first_int(pData CHAR(10))
RETURNS INT
BEGIN
DECLARE vPos INT DEFAULT 1;
DECLARE vRes INT DEFAULT 0;
DECLARE vChar INT;
WHILE vPos <= LENGTH(pData) DO
SET vChar = ASCII(SUBSTR(pData,vPos,1));
IF vChar BETWEEN 48 AND 57 THEN
RETURN vPos;
END IF;
SET vPos = vPos + 1;
END WHILE;
RETURN NULL;
END//
DELIMITER ;//
结果:
mysql> SELECT id,myWord,find_first_int(myWord) AS myPos FROM t1;
+------+--------+-------+
| id | myWord | myPos |
+------+--------+-------+
| 1 | AB123 | 3 |
| 2 | A413D | 2 |
| 3 | X5231 | 2 |
| 4 | ABE921 | 4 |
| 5 | ABC | NULL |
+------+--------+-------+
可以使用函数IFNULL()更改NULL结果.
请注意,该函数只接受CHAR(10),因此您可能希望更改更长的数据.