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

Solution 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

Solution 2

REGEXP_INSTR is close in functionality to PatIndex, but you will need to be running MySQL 8.0 or later to use it.

8.0 was released April 2018. Confusingly, the last version released before 8.0 was 5.7. Wikipedia explains why.