query designs using Sql wildcards

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

lordharliquin - 20 May 2005 19:18 GMT

I am having problems getting my database to return all of the results I need.
I know it's a coding error, but I don't know how to fix it. I have a larg
table with multiple results [i.e. (BIO:104), (BIO:104, BIO:111), (BIO:111,
BIO:210), etc.]. How do I get the engine to return not only the single values
(like BIO:104) but also the multiple values including the selected value
(like BIO:104, BIO:###, BIO:###)?

I've tried using:
WHERE [College Courses]='BIO:104', 'BIO:104, BIO:###','BIO:104, BIO:###,
BIO:###'
but it doesn't work.

Reply to this Message

John Vinson - 21 May 2005 05:55 GMT

>I am having problems getting my database to return all of the results I need.
>I know it's a coding error, but I don't know how to fix it. I have a larg
[quoted text clipped - 7 lines]
>BIO:###'
>but it doesn't work.

The LIKE operator recognizes wildcards but the = operator does not.
Try changing the = to LIKE. Also, you can't just put a list of
criteria; you need to use the OR operator to combine logical
expressions.

WHERE [College Courses] LIKE 'BIO:104' OR WHERE [College Courses] LIKE
'BIO:104, BIO:###' OR WHERE [College Courses] LIKE
'BIO:104, BIO:###, BIO:###'

If you want to find BIO:104 wherever it occurs in the string - as it
appears that you may - use the * wildcard to match any set of
preceding or following characters:

WHERE [College Courses] LIKE '*BIO:104*'

And... normalize your data. This field is going to give you endless
trouble.

John W. Vinson[MVP]

Reply to this Message