Hello,

I was hoping someone could help me with this SQL statement.

The column 'options.optionDescrip' is a varchar field.

Some values of the 'options.optionDescrip' contain commas, some do
not.

We are trying to evaluate against any data in the
'options.optionDescrip' column located to the left of a comma (if a
comma does exist, which it may not). If no comma exists, then we try
and evaluate against the entire field, not just the part to the left
of the comma.

SELECT options_optionsGroups.idProduct FROM options_optionsGroups
JOIN Options ON options_optionsGroups.idOption=options.idOption
JOIN products ON options_optionsGroups.idProduct=products.idProduct
WHERE (CASE WHEN CharIndex(',',options.optionDescrip) = 0 THEN
options.optionDescrip LIKE '" & gauge & "%' ELSE
Left(options.optionDescrip,CharIndex(',',options.o ptionDescrip)) LIKE
'" & gauge & "%' END)

Thanks for any input you can provide, I appreciate it.

On Thu, 14 Feb 2008 15:16:21 -0800 (PST), jeremy (AT) flatironsinternet (DOT) com
wrote:

One option is to add a comma at the end of your field, so there always
is at least one:
SELECT LEFT(options.optionDescrip,
CHARINDEX(',',options.optionDescrip+',')-1)
FROM Options

-Tom.

The CASE function returns an expression and it cannot be used directly as
predicate in the WHERE clause. Here is how you can change the CASE
expression to form a valid WHERE filter (btw, I assume based on the
concatenation method this is a query send from client app, so let that
intact):

SELECT G.idProduct
FROM options_optionsGroups AS G
JOIN Options AS O
ON G.idOption = O.idOption
JOIN products AS P
ON G.idProduct = P.idProduct
WHERE
(CASE WHEN CHARINDEX(',', O.optionDescrip) = 0
THEN O.optionDescrip
ELSE
LEFT(O.optionDescrip, CHARINDEX(',', O.optionDescrip))
END) LIKE '" & gauge & "%'

I think you can also write the query as below, but you have to test with
your data, and not sure will have any effect on performance.

SELECT G.idProduct
FROM options_optionsGroups AS G
JOIN Options AS O
ON G.idOption = O.idOption
JOIN products AS P
ON G.idProduct = P.idProduct
WHERE O.optionDescrip + ',' LIKE '" & gauge & "%,%'

HTH,

Plamen Ratchev
http://www.SQLStudio.com

Thank you very much for taking the time to answer my question, the
solution works great.


On Feb 14, 8:21*pm, "Plamen Ratchev" <Pla... (AT) SQLStudio (DOT) com> wrote: