Post reply

PATINDEX equivalent

  • 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!

  • March 20, 2012 at 9:10 am

    #1461694

    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

  • March 20, 2012 at 9:13 am

    #1461695

    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

    #1461710

    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]

  • March 20, 2012 at 9:33 am

    #1461711

    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

  • March 20, 2012 at 9:37 am

    #1461717

    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

    #1461764

    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