Recent Postings

Recent Comments


RSS2 feed


Most Popular Posts

MySQL equivalent of PATINDEX

Thursday, 31st May 2007 1:17pm
One of the Transact-SQL expressions that I use quite often in SQL Server is PATINDEX which I use to find the starting position of the first occurrence of a pattern in a specified expression.

To find the starting position of say pattern 'ev' in 'web development' just as an example, you would use:

SELECT PATINDEX('%ev%', 'web development')

In MySQL you have a choice of two functions to use, POSITION and LOCATE. Using POSITION in MySQL the above example would be :

SELECT POSITION('ev' IN 'web development');

I think LOCATE is a better function because it allows you to specify an optional starting position to begin the search and also because its usage is similar to the T-SQL equivalent.
Using LOCATE without specifying the starting position above example would like this:

SELECT LOCATE('ev', 'web development');

and specifying a starting point of say 4 as an example:

SELECT LOCATE('ev', 'web development', 4);

ercan wrote on Saturday, 17th April 2010 4:24pm
Actually these functions are not equivalent to PATINDEX, because they do not allow regexp-like search like PATINDEX does: SELECT PATINDEX('%[a-z]%', '1212abc34') -- returns 5

Damian wrote on Wednesday, 24th November 2010 4:40am
And for mssql "difference" what could I use?

Wes wrote on Saturday, 27th November 2010 6:50am
Actually I don't know the mysql equivalent of mssql's "difference".

Post a comment:



(required, but not published)


Notify me of follow-up comments via e-mail