PATINDEX equivalent

Question

PATINDEX equivalent

Viewing 6 posts - 1 through 7 (of 7 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: 77014 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