March 19, 2012 at 2:06 am
I know that probably this is not the best place to ask this question, but just in case someone has any ideas.
I have a query from MSSQL to convert in MYSQL syntax.
I have some problems finding an equivalent for PATINDEX. I need a function/query to return the position of a pattern in a string. From what I could find the somewhat equivalent of Patindex in Mysql is the Locate function, but this function does not return the position of a pattern. It will only return the position of a char/string.
Ex: 150 grammes de farine ordinaire,10 cl de vin blanc doux,1,5 kg de coings,2 citrons,1 bâton de cannelle
I need the position of the pattern [0-9],[0-9] so I can replace ',' with '@'
The only situation in wich the comma should be replaced is when it is between digits.
At this moment my replace part of the query looks like this:
stuff(cast(ingredients as varchar(max)), charindex(',', ingredients,(PATINDEX('%[0-9],[0-9]%',ingredients))), 1, '@')
Is there another way I can change it so that I don't use PATINDEX?
Every suggestion will be extremely appreciated,
Thank you!
March 20, 2012 at 9:10 am
While I have never worked with MySQL, looking at the function syntax for it you could use the REGEXP function which looks very close to PATINDEX.
Don Urquhart
March 20, 2012 at 9:13 am
REGEXP is not ok for my case, because it will only return 1/0 (if the pattern is found inside the string or not) and it does not return the position of the pattern.
March 20, 2012 at 9:31 am
Try to post on MYSQL forum, it should be the one somewhere;-)
March 20, 2012 at 9:33 am
Sorry, missed that.
Looking at the existing functions there isn't one to use. I think you would have to write your own function and do some kind of char. by char. string processing to find the #,# pattern you are looking for.
You might be able to look for one that someone else has written.
Don Urquhart
March 20, 2012 at 9:37 am
Yes..I know 🙂
And I havepost it on Mysql forums, but it seems there isn't an easy way to do that.
There isn't any syntax in MySQL for extracting text using regular expressions.
And I also made the post here maybe to find an alternative to do my Replace without using PATINDEX, so that I could translate into Mysql syntax.
March 20, 2012 at 10:26 am
I don't know MySQL either, but here is an approach using a Tally table. (I may have the syntax for REGEX wrong.)
SELECT Min(n) AS n
FROM YourTable
INNER JOIN Tally
ON Len(YourTable.YourString) >= Tally.n + 2
WHERE REGEX('%[0-9],[0-9]%',LEFT(YourString, n + 2))
GROUP BY YourTable.YourString
(There's no sense testing the full length string and the next smaller substring, because the search pattern will go past the end of the string before matching when it starts at those positions.)
Drew
Edited: to add group by and to correct ON clause.
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply