tclogo



HotelsCombined.com

Pages

Archives

Categories


Recent Postings

Recent Comments

Feeds

RSS2 feed

Links

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

Post a comment:

 

(required)

(required, but not published)

(optional)





Notify me of follow-up comments via e-mail

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

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