sql - PATINDEX() replacement in MYSQL

Question

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

Answer 1

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