MySQL equivalent of PATINDEX
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);
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
reply
And for mssql "difference" what could I use?
reply
Actually I don't know the mysql equivalent of mssql's "difference".
reply