469,138 Members | 1,284 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,138 developers. It's quick & easy.

CharIndex, Left T-SQL question

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.
Feb 14 '08 #1
1 5991
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.
Feb 15 '08 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

7 posts views Thread by Wayne Wengert | last post: by
4 posts views Thread by Harlan Messinger | last post: by
2 posts views Thread by Little PussyCat | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.