SQL question with InStr function

[kaosyeti via AccessMonster.com]

i have a table that i'm pulling records from where i want to limit my results
by multiple criteria. i'm stuck on a particular field ([Ordered Options])
that has a list of option codes, all 3 characters long, separated by commas:

B37, C60, FE9, J41, L61, MN5, US8, 19G, 41U

what i want to do is to write an WHERE that will test to see if specific user-
selected option codes are within the field, and then pull those records. so
i have now a string variable strWhere that pulls an where clause that looks
like this:

([Model] = "ZV14526") and ([msrp] <= 30000)

and i want one that adds something like:

instr(1, [Ordered Options], <<user selected option code>>) > 0

so that if the option code is not in the field, the instr expression returns
0 and won't be in the where clause.

is what i'm describing possible, is there another (ie better) way to do it,
or did i not explain it well enough and you need more info? thanks for
looking.

greg

 

[Sylvain Lafontaine]

Now, you can see that storing a list of ordered options in a single field
instead of using one or more supplemental tables is more trouble than
anything else.

In your case, you could use the Like function (instead of the Instr
function) but the overall performance will drop like a ball because
SQL-Server will have to make a full table scan each time.

 

[Guest]

Try this LIKE ---
([Model] = "ZV14526") and ([msrp] <= 30000) And ([Ordered Options] Like
<<user selected option code>> &"*")
--
KARL DEWEY
Build a little - Test a little

Now, you can see that storing a list of ordered options in a single field
instead of using one or more supplemental tables is more trouble than
anything else.

In your case, you could use the Like function (instead of the Instr
function) but the overall performance will drop like a ball because
SQL-Server will have to make a full table scan each time.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

i have a table that i'm pulling records from where i want to limit my
results
by multiple criteria. i'm stuck on a particular field ([Ordered Options])
that has a list of option codes, all 3 characters long, separated by
commas:

B37, C60, FE9, J41, L61, MN5, US8, 19G, 41U

what i want to do is to write an WHERE that will test to see if specific
user-
selected option codes are within the field, and then pull those records.
so
i have now a string variable strWhere that pulls an where clause that
looks
like this:

([Model] = "ZV14526") and ([msrp] <= 30000)

and i want one that adds something like:

instr(1, [Ordered Options], <<user selected option code>>) > 0

so that if the option code is not in the field, the instr expression
returns
0 and won't be in the where clause.

is what i'm describing possible, is there another (ie better) way to do
it,
or did i not explain it well enough and you need more info? thanks for
looking.

greg

 

[kaosyeti via AccessMonster.com]

Karl,
that's perfect. i actually had to put the wildcard before and after the
option code to make it work but that's great:

([Model] = "ZV14526") and ([Ordered Options] Like "*PCM*")

or

([Model] = "ZV14526") and ([Ordered Options] Like "*PCM*") and ([Ordered
Options] Like "*PCQ*")

this seems to work well. and for the record, in response to sylvain's post,
i completely agree that it's a bad idea to put multiple items in one field.
unfortunately, the table itself is created by the user by clicking a button i
wrote to import an excel file, using the column headers as field names.
unfortunately, the excel file is created off of a manufacturer run website
and i don't know of any way to code the import of the table to separate each
option code (it can be between 4 and about 20) into its own field. i would
be very satisfied to re-write the field names before doing any sql's but i'm
only in 1 store of 4 that are using this database and the other people are
clueless to do things like that.

if you can think of a programatic (<-- is this actually a word?) way to
separate each option code into its own field, i'm all ears.

Thanks again for your help!

Try this LIKE ---
([Model] = "ZV14526") and ([msrp] <= 30000) And ([Ordered Options] Like

Now, you can see that storing a list of ordered options in a single field
instead of using one or more supplemental tables is more trouble than

[quoted text clipped - 36 lines]