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()

解决方案

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

Query (searches for first digit in string)

SELECT mystring, PatIndex('[0-9]', mystring) As FirstNumberCharacter

FROM myTable