SQL Server - How to find data in a column which contains multiple values.

Asked By Priyanka on 30-Jul-13 02:58 PM
i want to retrieve that records from database in which match column contains 'single-101'.

i am storing multiple values in match column like single-101,single-102,single-103 etc. And it is also possible that it can contain only a single values like 'single-101'.

i know how to retreive when it is single value in match column :

OleDbCommand cmd = new OleDbCommand("select * from Table1 where match = 'single-101'", con);

but what if there are multiple values in the match column.. Then i know above query will not work. 

Please suggest me how to do this...

I will be very thankful of yours.
Robbe Morris replied to Priyanka on 30-Jul-13 03:38 PM
This is what is called "multi-valued" fields and is considered one of the seven deadly sins of database design.  Ever mention you do this in a interview and you can kiss that job opportunity goodbye.

Not saying "you" designed it this way.  Just emphasizing how bad this design is.

You've really only got one choice and it ain't all that great.  The LIKE clause.

where somecolumn like '%single-101%'

Of course, this starts causing problems with values like single-1012