CODEWITHSUNDEEP
sqlsql-serverlinqlinq-to-sql
Maddhacker24
Maddhacker24

Reputation: 1875

What's the best way to query a column to see if it contains a particular number? The column is varchar

I have a table with a column that includes a handful of numbers delimited by a comma. I need to select * rows that include a particular value. I am using SQL Server and C# so it can be in SQL or LINQ.

The data in my channels column (varchar) looks something like this: 1,5,8,22,27,33

My Media table looks like this:

MediaID MediaName MediaDate ChannelIDs
------- --------- --------- ----------
1 | The Cow Jumped Over The Moon | 01/18/2015 | 1,5,8,22,27,33
2 | The Cat In The Hat | 01/18/2015 | 2,4,9,25,28,31
3 | Robin Hood The Thief | 01/18/2015 | 3,5,6,9,22,33
4 | Jingle Bells Batman Smells | 01/18/2015 | 6,7,9,24,25,32
5 | Up The River Down The River | 01/18/2015 | 5,6,10,25,26,33

etc...

My Channels Table looks like this:

ChannelID ChannelName
--------- -----------
1 Animals
2 Television
3 Movies
4 Nursery Rhymes
5 Holidays

etc...

Each row of Media could contain multiple channels.

Should I be using a contains search like this?

SELECT * FROM Media WHERE CONTAINS (Channels,'22')

This would require me to full-text index this column but I don't really want to include this column in my full-text index.

Is there a better way to do this?

Thanks

Upvotes: 1

Views: 77

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270463

You should fix your data format so you are not storing numbers as comma-delimited strings. SQL has a great data structure for lists, it is called a table not a string. In particular, you want a junction table with one row per "media" entity and id.

That said, sometimes you are stuck with a particular data structure. If so, you can use like:

where ','+channels+',' like '%,22,%'

Note: this cannot take advantage of regular indexes, so performance will not be good. Fix the data structure if you have a large table and need better performance.

Upvotes: 6

Related Questions