2 Replies Latest reply: Jul 1, 2013 4:57 PM by 1006421 RSS

    Dynamic WHERE Clause with Multiple ORs

    CycleGeek

      I need to build a SQL statement dynamically such that the WHERE clause looks/works as follows:

       

      WHERE x LIKE '%1%' OR x LIKE '%2%' OR x LIKE '%3%' ....

       

      I can do the above no problem, but I'm wondering if there's a better way?

       

      Thanks,

      Mark

        • 1. Re: Dynamic WHERE Clause with Multiple ORs
          Frank Kulash

          Hi, Mark,

           

          You shouldn't need dynamic SQL for that.

           

          For example, if you're always interested in suingle-character substrings, and you're getting the input as a single string called str (e.g. '123' meaning you want to find rows where x contains '1', '2' or '3'), then you could say:

           

          ...

          WHERE   REGEXP_LIKE ( x

          , '[' || str || ']'

          )

           

           

          I hope this answers your question.
          If not, post a little sample data (CREATE TABLE and INSERT statements), a few inputs, and also post the results you want from each input given the same data.
          Explain, using specific examples, how you get those results from that data.
          Always say which version of Oracle you're using (e.g., 11.2.0.2.0).

          See the forum FAQ: https://forums.oracle.com/message/9362002#9362002

          • 2. Re: Dynamic WHERE Clause with Multiple ORs
            1006421

            To expand on Frank's suggestion a bit (and I may be understanding your requirement differently than he), the following may suit your needs:

             

            WHERE REGEXP_LIKE(x, '[a-z A-Z][0-9][a-z A-Z]')

             

            Based on the example you provided, it seems like your goal might be to return records where a single whole number exists that is preceeded and proceeded by something (in this case, I'm going to assume one or more uppercase or lowercase letters on either side).