Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2012
    Posts
    4

    Unanswered: Patindex equivalent in MySSQL

    I am new at MYSQL and 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.

    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.

    Every suggestion will be extremely appreciated,
    Thank you!

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    have a look at the MySQL string functions
    there';s plenty to choose form including replace you could eevn use regular expressions
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Mar 2012
    Posts
    4
    I have looked, but I couldn't find a funtion to return the position of the pattern.
    REGEXP, will only return 1/0, if the pattern exists in the string or not, from what I could see

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    try instr()
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Mar 2012
    Posts
    4
    INSTR() does not seem to work out. It returns 0 every time.
    I have used it like this:

    INSTR(column_name, '[0-9],[0-9]')

    And I am looking for the position of the pattern "digit,digit" in the string column_name.

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I don't think there is an appropriate out of the box function in MySQL for what you want. I can think of a workaround using a numbers table, but this is not going to be efficient!

    Something like this (untested)...
    Code:
    SELECT your_table.col
         , Substring(your_table.col FROM numbers.number FOR 3)
         , Substring(your_table.col FROM numbers.number FOR 3) RegExp '[0-9],[0-9]'
    FROM   your_table
     INNER
      JOIN numbers
        ON numbers.number BETWEEN 0 AND Length(your_table.col)
    George
    Home | Blog

  7. #7
    Join Date
    Mar 2012
    Posts
    4
    Thank you for all your help!

    I have finally found a solution, but only tested with a SELECT statement and it seems to work ok.
    My only remaining problem is that I can't seem to transform the SELECT statement into an UPDATE statement.

    This is the SELECT statement:

    SELECT id_recette, ingredients
    ,insert(ingredients, min(id) + 1,1, '@')
    FROM recettes
    INNER JOIN tally
    ON Length(recettes.ingredients) >= tally.id + 2
    WHERE LEFT(ingredients, id + 2) regexp '[0-9],[0-9]'
    GROUP BY ingredients

    I did found a way to use the UPDATE statement with Joins, but it doesn't seem to work because of the GROUP BY.
    I cannot lose the GROUP BY statement, because the output will be totally different.

    Thank you so much!
    Last edited by Ralu; 03-21-12 at 11:09.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •