在MYSQL中的PATINDEX()替换 [英] PATINDEX() replacement in MYSQL
问题描述
我要转换一个sql server script,我需要在MYSQL中转换PATINDEX()。你会建议哪个MYSQL函数类似于PATINDEX()
i am converting a sql server script where i need to convert PATINDEX() in MYSQL. would you please suggest which MYSQL function is similar to PATINDEX()
推荐答案
虽然没有正式的 PATINDEX ()
函数在MySQL中实现正则表达式模式查找与返回的字符索引,或 LOCATE()
和 REGEXP()
,考虑一个用户定义函数,它循环遍历字符串长度中的每个字符,并检查字符上的 REGEXP
。一旦创建,使用查询的内联函数。
While there is no formal PATINDEX()
function in MySQL that achieves both the regex pattern lookup with returned character index, or a combination of LOCATE()
and REGEXP()
, consider a User-Defined function that loops through each character in the length of a string and checks a REGEXP
pattern on the character. Once created, use such a function in-line of a query.
DROP FUNCTION IF EXISTS PatIndex;
DELIMITER $$
CREATE FUNCTION PatIndex(pattern VARCHAR(255), tblString VARCHAR(255)) RETURNS INTEGER
DETERMINISTIC
BEGIN
DECLARE i INTEGER;
SET i = 1;
myloop: WHILE (i <= LENGTH(tblString)) DO
IF SUBSTRING(tblString, i, 1) REGEXP pattern THEN
RETURN(i);
LEAVE myloop;
END IF;
SET i = i + 1;
END WHILE;
RETURN(0);
END
查询(搜索字符串中的第一个数字)
SELECT mystring, PatIndex('[0-9]', mystring) As FirstNumberCharacter
FROM myTable
这篇关于在MYSQL中的PATINDEX()替换的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!