CharIndex, Left T-SQL question

[jeremy@flatironsinternet.com]

Hello,

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

The column 'options.option Descrip' is a varchar field.

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

We are trying to evaluate against any data in the
'options.option Descrip' 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_options Groups.idProduc t FROM options_options Groups
JOIN Options ON options_options Groups.idOption =options.idOpti on
JOIN products ON options_options Groups.idProduc t=products.idPr oduct
WHERE (CASE WHEN CharIndex(',',o ptions.optionDe scrip) = 0 THEN
options.optionD escrip LIKE '" & gauge & "%' ELSE
Left(options.op tionDescrip,Cha rIndex(',',opti ons.optionDescr ip)) LIKE
'" & gauge & "%' END)

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

[Tom van Stiphout]

Re: CharIndex, Left T-SQL question

On Thu, 14 Feb 2008 15:16:21 -0800 (PST), jeremy@flatiron sinternet.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.op tionDescrip,
CHARINDEX(',',o ptions.optionDe scrip+',')-1)
FROM Options

-Tom.

>Hello,
>
>I was hoping someone could help me with this SQL statement.
>
>The column 'options.option Descrip' is a varchar field.
>
>Some values of the 'options.option Descrip' contain commas, some do
>not.
>
>We are trying to evaluate against any data in the
>'options.optio nDescrip' 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_options Groups.idProduc t FROM options_options Groups
>JOIN Options ON options_options Groups.idOption =options.idOpti on
>JOIN products ON options_options Groups.idProduc t=products.idPr oduct
>WHERE (CASE WHEN CharIndex(',',o ptions.optionDe scrip) = 0 THEN
>options.option Descrip LIKE '" & gauge & "%' ELSE
>Left(options.o ptionDescrip,Ch arIndex(',',opt ions.optionDesc rip)) LIKE
>'" & gauge & "%' END)
>
>Thanks for any input you can provide, I appreciate it.