11

I have to select all the records where:

where FIELD2 like '%value21%'
or FIELD2 like '%value22%'
or FIELD2 like '%value23%'
-- repeat up to
or FIELD2 like '%value29%'

Here, value21, ..., value29 are parameters which the user can put in a form before submitting the query. They are (not subsequent) numerical codes, and FIELD2 is a database column holding a string value.

Which is the most compact form to write down my SQL query?

Note: This is related to an earlier question, but this needs LIKE rather than equals.

Community
  • 1
  • 1
tic
  • 4,009
  • 15
  • 45
  • 86
  • 1
    It would help us immensely if you tell us what `value21..value29` is – Sathyajith Bhat Oct 14 '10 at 17:45
  • @Arjan, you are right: I have updated my question. It is too difficult for me now to explain the database structure. Do you have any specific question? –  Oct 14 '10 at 17:56
  • @Sathya, value21,...,value29 are (not subsequent) numerical codes which the user can put in a form before submitting the query. –  Oct 14 '10 at 17:58
  • 1
    I meant: what database vendor/dialect? And you probably want the best performing query, not the most compact form. – Arjan Oct 14 '10 at 18:57
  • 1
    @Arjan: there's no way to get good performance out of a query like this. By definition (col like '%val%') can't make use of any indexes, so it's going to be a table scan. – NotMe Oct 14 '10 at 19:01
  • I agree with both @Larry Lustig and @Chris Lively, however I would go with @Chirs's option. If you are just matching FIELD2 then value 1 thru 29 will be compared to one field, however, I believe in your case you are wanting to show the individual results for each 29 parameter so with Chris's option you'll be able to identify which result goes to which search parameter on a single form...hope I explained myself. – Saif Khan Oct 14 '10 at 19:12
  • Just making a wild guess about `FIELD2`: if that actually only holds one numerical value, like `ABC123XYZ` which you want to match to `123` but NOT to `12` or `23`, then maybe first extract the number from that `FIELD2`, and use `IN(..)` instead of `LIKE`. – Arjan Oct 14 '10 at 19:24

2 Answers2

21

I'm afraid you're stuck with:

  WHERE (FIELD2 LIKE '%value21' OR 
         FIELD2 LIKE '%value22' OR 
         FIELD2 LIKE '%value23' ...)

at least in standard SQL (your particular engine might offer some form of full-text indexing that would help).

A query like this often indicates a normalization problem in your database design with multiple values stored in a single field value. If that's true in your case and you have any degree of control over the database schema, I advise fixing the problem as soon as possible. Otherwise check your medical coverage to make sure it covers SQL-induced psychosis — this can drive you crazy.

Larry Lustig
  • 49,320
  • 14
  • 110
  • 160
1

One way:

select Field1, Field2 from Table where Field2 like '%val%'
UNION
select Field1, Field2 from Table where Field2 like '%val2%'
UNION
select Field1, Field2 from Table where Field2 like '%val2%'

etc.

NotMe
  • 87,343
  • 27
  • 171
  • 245
  • Not UNION ALL, I think, since some rows may match several different conditions. I think a "regular" UNION as long as the PK is included in the list of selected fields. – Larry Lustig Oct 14 '10 at 21:10
  • @Larry: adjusted accordingly. thanks – NotMe Oct 14 '10 at 21:15