• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 597
  • Last Modified:

Combining LIKE and IN

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.
2 Solutions
Raja Jegan RSQL Server DBA & ArchitectCommented:
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.
Guy Hengel [angelIII / a3]Billing EngineerCommented:
an alternative syntax is possible, but won't be more efficient or shorter code.
mbonaciAuthor Commented:
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?
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

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
mbonaciAuthor Commented:
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 :)
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 t1.cl like  '%cl.'||char(t2.offense_id)+'%'
where t2.provision_id = 3

or something like that ?
mbonaciAuthor Commented:
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,
mbonaciAuthor Commented:
Thank you (especially Momi), I'll surely be back with more of my dilemmas...
mbonaciAuthor Commented:
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now