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.
LVL 22
Who is Participating?
momi_sabagConnect With a Mentor Commented:
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 ?
Raja Jegan RConnect With a Mentor SQL 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.
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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?
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 :)
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...
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.