CODEWITHSUNDEEP
mysqlsqlsql-serverdatabase
Supriya Roy
Supriya Roy

Reputation: 57

PATINDEX() replacement in MYSQL

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

Upvotes: 2

Views: 20021

Answers (3)

Cristian Andres Marin Durango
Cristian Andres Marin Durango

Reputation: 1

This is my solution :D

ORDER BY
CASE
WHEN instr([column], '[text]') > 0 THEN
  instr([column], '[text]')
WHEN instr([column], '[text]') = 0 THEN
  100(//large number) end ASC

Upvotes: -1

Winter Dragoness
Winter Dragoness

Reputation: 1325

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.

Upvotes: 3

Parfait
Parfait

Reputation: 107567

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

Upvotes: 1

Related Questions