PATINDEX equivalent

Question

PATINDEX equivalent

ralu_k_17

SSCrazy

Points: 2190
More actions
March 19, 2012 at 2:06 am

#265852

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!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Don Urquhart SSCrazy Points: 2423 More actions · Answer 1

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.

If you don't stand for something, you'll fall for anything!,

Don Urquhart

ralu_k_17 SSCrazy Points: 2190 More actions · Answer 2

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.

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 3

Try to post on MYSQL forum, it should be the one somewhere;-)

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Don Urquhart SSCrazy Points: 2423 More actions · Answer 4

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.

If you don't stand for something, you'll fall for anything!,

Don Urquhart

ralu_k_17 SSCrazy Points: 2190 More actions · Answer 5

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.

drew.allen SSC Guru Points: 76985 More actions · Answer 6

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