CharIndex, Left T-SQL question

[jeremy]

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.

[Tom van Stiphout]

On Thu, 14 Feb 2008 15:16:21 -0800 (PST), je****@flatironsinternet.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.

>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. optionDescrip)) LIKE
'" & gauge & "%' END)

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