1 Reply Latest reply: May 20, 2011 4:47 PM by crj RSS

    filter a value from a field

    783492
      hi, we have a fields like - blabla (hello) - i need the word in the ( ), so the word hello. how can i do that in the report with a function?

      thanks
      carmelo
        • 1. Re: filter a value from a field
          crj
          I'm not sure if anyone's still watching this thread (as the question was posted a while back)... but here is a solution:

          SUBSTRING('blabla (hello)' FROM POSITION('(' IN 'blabla (hello)') + 1 FOR POSITION(')' IN 'blabla (hello)') - POSITION('(' IN 'blabla (hello)') - 1)

          The POSITION function gives you the first occurrence of a string within another string. The SUBSTRING function is used to get a subset of the characters in the string. So, ultimately, we're telling On Demand to give us characters x through y of the string, and X & Y are calculated with the POSITION function (which is used to find the "(" and ")" characters.

          You would replace the instances of 'blabla (hello)' with the actual field name. Note -- if you have two strings within parenthesis (e.g. 'blabla (hello1) (hello2)' ), this function will only give you the first (hello1).

          Chris