以下是我在表myTable中的内容
+++++++++++++++
+ id + myWord +
+++++++++++++++
+ 1 + AB123 +
+ 2 + A413D +
+ 3 + X5231 +
+ 4 + ABE921 +
+++++++++++++++
当我执行
SELECT id,Locate('1',myWord) as myPos
FROM myTable;
我得到1的位置.
+++++++++++++++
+ id + myPos +
+++++++++++++++
+ 1 + 3 +
+ 2 + 3 +
+ 3 + 5 +
+ 4 + 6 +
+++++++++++++++
我想要实现的是找到整数的第一个位置,这样我将得到低于输出.
+++++++++++++++++++++++
+ id + myWord + myPos +
+++++++++++++++++++++++
+ 1 + AB123 + 3 +
+ 2 + A413D + 2 +
+ 3 + X5231 + 2 +
+ 4 + ABE921 + 4 +
+++++++++++++++++++++++
任何想法我怎么能做到这一点？
最佳答案
如果你经常使用MysqL,最好使用string functions SUBSTRING()和ASCII()制作Stored Function.
原文链接：https://www.f2er.com/mysql/433995.htmlDELIMITER //;
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 |
+------+--------+-------+
请注意,该函数只接受CHAR(10),因此您可能希望更改更长的数据.