Combining LIKE and IN

Posted on 2009-04-18
Last Modified: 2012-05-06
Can I write the query simpler than this:

... WHERE  cl LIKE '%cl.%100%'
                 OR cl LIKE '%cl.%101%'
                 OR cl LIKE '%cl.%105%'
                 OR cl LIKE '%cl.%120%'
                 OR cl LIKE '%cl.%122%'
                 ... and 50 more

I tried something like this:

... WHERE  cl LIKE IN ('%cl.%100%','%cl.%101%','%cl.%105%','%cl.%120%','%cl.%122%')

It's DB2 v9.5 and I'm SQL/DB2 newbie.

Thank you.
Question by:mbonaci
    LVL 57

    Assisted Solution

    by:Raja Jegan R
    You wont be able to merge IN and LIKE operators together.
    Since it has to be separate, no other go other than first query.

    But that would be an impact on the performance.
    If you need to run this query often, then have another column in the table as Flag and while inserting records, mark it appropriately so that you can select records only for that particular flag to improve performance. That's the only way to improve performance of the above query.
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    an alternative syntax is possible, but won't be more efficient or shorter code.
    LVL 22

    Author Comment

    Thank you for that. Now I know for sure it cannot be done as I imagined.

    It has just came to my mind, would it be possible to convert the number part of string to integer and then use less then on greater then operators?
    If yes, can you give me an example of what the syntax would look like?
    LVL 37

    Expert Comment

    can you provide samples of your data and what you need to achieve?
    this way i would be able to give you my best advice
    LVL 22

    Author Comment

    I'm creating report for an application whose data is originally in Domino db, but is one-way synced with db2 for the purpose of mounting Jasper reporting engine.

    I have the registry of all offenses (along with their articles and paragraphs) of the Criminal act and I have a table with "cl" field, where the description of single offense is located, structured like this:

    <name of the offense> "cl." <offense article> "st." <offense paragraph>

    Where <offense article> is always string in the form of Integer.
    The quotation marks mark fixed strings, with an exception of not knowing whether there would be space sign between "cl." and <offense article>.

    The report needs to establish the legal provision of current offense.
    Each provision has multiple articles, e.g.:

    Provision I:
    article 100, paragraph 1 ... (desc)
    article 100, paragraph 2 ...
    article 100, paragraph 3 ...

    article 101, paragraph 1 ...
    article 101, paragraph 2 ...

    Provision II:
    article 102, paragraph 1 ...

    article 103, paragraph 1 ...
    article 103, paragraph 2 ...

    Provision III:
    article 104, paragraph 1 ... (desc)
    article 104, paragraph 2 ...

    article 105, paragraph 1 ...
    article 105, paragraph 2 ...
    article 105, paragraph 3 ...

    article 106, paragraph 1 ...

    article 107, paragraph 1 ...
    article 107, paragraph 2 ...
    article 142, paragraph 1 ...

    Provision IV:

    Now, how to establish whether the current offense belongs to provision III (where I have 142 articles)?

    * Keep in mind that tables are not to be altered with new columns (meta data is created by Java SP that reads Domino design. "Sync is not sync", but data pull from DB2 SP, and it's always complete, not incremental, so the whole table is first cleared).

    ** And one more thing, I don't think we should hard code the articles in the Query. It would be better to compare it with table in which we'll place the whole Criminal act. How? (I'll open another question for this)

    Don't look me like that, you asked for it :)
    LVL 37

    Accepted Solution

    so, since you have the values you want in a table you can try something like

    select *
    from mytable t1 join  table_with_provision_offenses t2
     on like  '%cl.'||char(t2.offense_id)+'%'
    where t2.provision_id = 3

    or something like that ?
    LVL 22

    Author Comment

    Yes, that's a great suggestion.
    I don't have "table_provision_offenses", the articles were forged (hard-coded) into the query, so my first example was the real code.
    I'll create offenses table and try this first thing tomorrow morning.

    Since I can already see your suggestion will work, I'm accepting it as the solution.

    Thank you,
    LVL 22

    Author Closing Comment

    Thank you (especially Momi), I'll surely be back with more of my dilemmas...
    LVL 22

    Author Comment

    I forgot one thing,
    in the report, there are multiple categories inside one provision.

    So I, nevertheless, need to compare articles.
    I'm opening another question...

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
    Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    25 Experts available now in Live!

    Get 1:1 Help Now