IN statement with CSVs in VARCHAR field contents

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

IN statement with CSVs in VARCHAR field contents

Author

Topic

rickeyFitts
Starting Member

4 Posts

Posted - 2004-08-18 : 12:41:39

I need a little help with something:

A web form that includes a group of checkboxes is populating a SQL Server table.
Each checkbox represents a numbered choice:

1 = chicken
2 = fish
3 = beef
4 = pork

A comma-delimited list of the numbers based on the user's selection is inserted
into the VARCHAR field reg_meat.

So if an user checked chicken, fish and pork, reg_meat would contain the string
'1','2','4'

The problem I'm having is in writing a query to output separate columns for
each choice with an "X" appearing for each row where the respective meat was
selected.

Here's what seems like should work (but doesn't):

SELECT
CASE
WHEN '1' IN (reg_meat) THEN 'X'
WHEN '1' NOT IN (reg_meat) THEN ''
END AS reg_chicken,
CASE
WHEN '2' IN (reg_meat) THEN 'X'
WHEN '2' NOT IN (reg_meat) THEN ''
END AS reg_fish,
CASE
WHEN '3' IN (reg_meat) THEN 'X'
WHEN '3' NOT IN (reg_meat) THEN ''
END AS reg_beef,
CASE
WHEN '4' IN (reg_meat) THEN 'X'
WHEN '4' NOT IN (reg_meat) THEN ''
END AS reg_pork
FROM tbl_registration

If
SELECT
CASE
WHEN '1' IN ('1','2','4') THEN 'X'
WHEN '1' NOT IN (reg_meat) THEN ''
END AS reg_chicken...
works,
why not

SELECT
CASE
WHEN '1' IN (reg_meat) THEN 'X'
WHEN '1' NOT IN (reg_meat) THEN ''
END AS reg_chicken...

if the contents of the reg_meat field is '1','2','4'?

Thanks in advance for any assistance!

nr
SQLTeam MVY

12543 Posts

Posted - 2004-08-18 : 12:50:58

when ',' + reg_meat + ',' like '%,1,%' then 'X'
...

or do a search for the other hundreds of times this question has been answered this year.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

rickeyFitts
Starting Member

4 Posts

Posted - 2004-08-18 : 13:29:27

Thanks, for your quick reply, NR, but that doesn't work...

What if the user only selected Chicken? The contents of the reg_meat field would be '1'. Or what if there were more than 10 choices? 11 would put an 'X' in the first column.

I could modify the way the data is stored to ensure that the last item in the list is always followed by a comma, or put in a bunch of additional WHENs (and I might end up doing if all else fails), but I am really hoping to understand why IN isn't working, and if there is a better approach, not messier (which is why I tried to present the challenge as clearly and completely as I could).

Also, I've been searching high and low for info on this both here, and in a number of other forums (and books). The closest I've found are a few discussions about problems passing a CSV string from a VARCHAR field to a stored procedure, but I couldn't seem to relate it to this problem. Can you at least point me to one or two of the hundred answers to which you refer?

Or can you suggest a search string - I've tried various combinations of delimited, varchar, IN, etc. As you can see I tend to be pretty wordy and have a hard time determining a few keywords that are relevant.

Thanks!

nr
SQLTeam MVY

12543 Posts

Posted - 2004-08-18 : 13:34:57

reg_meat = 1 would give a match for 1
reg_meat = 11 would not give a match for 1

if that's what you mean
i.e.
,11, is not like %,1,%

>> I could modify the way the data is stored to ensure that the last item in the list is always followed by a comma
Look at the query - it adds a comma to the start and end of the field for the match.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

rickeyFitts
Starting Member

4 Posts

Posted - 2004-08-18 : 13:51:05

Yes, I stand corrected -- very good! You are basically adding the leading and trailing commas to the field before doing the comparison. I should have looked closer at your example... thanks!

Meanwhild, any ideas on why the IN approach won't work?
I feel like there is some fundamental understanding that I am missing..

Thanks again, nr.

nr
SQLTeam MVY

12543 Posts

Posted - 2004-08-18 : 14:20:43

in (1,2,3)
is not the same as
in ('1,2,3')

1 in (1,2,3) is successful
'1,2,3' in ('1,2,3') is also successful.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

rickeyFitts
Starting Member

4 Posts

Posted - 2004-08-18 : 14:53:46

Agreed, but the problem is that
whether the VARCHAR field reg_meat contains 1,2,3 '1,2,3' or '1','2','3'
...IN(reg_meat) is not successful.

Why is this?

... IN(reg_meat) is not successful

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-18 : 15:02:51

You would need IN ('1','2','3')

IN (1,2,3) is for integers. You wouldn't have data like this: IN ('1,2,3')

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources