CSV In A Column - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

CSV In A Column

Author

Topic

sam13
Starting Member

6 Posts

Posted - 2008-05-06 : 05:56:39

I have a column in a table in which the values are separated with comma like this

fruits
-------------------
apple,banana,orange,mango

grapes,orange,lemon

banana,cherrys,pineapple

I want to search that column when the user enters a specific item in a textbox. How i do this with T-SQL?. Any help is much appreciated.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-05-06 : 06:11:08

What should be the desired output? Can you show us an example?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

pootle_flump

1064 Posts

Posted - 2008-05-06 : 06:22:33

Hi Sam

Are you aware that this design breaks standard database design principles? If not then please read the below - it might seem like a pain now but it will save you countless hours in the future instead of struggling to work with a poor design.
http://www.tonymarston.net/php-mysql/database-design.html

sam13
Starting Member

6 Posts

Posted - 2008-05-06 : 06:30:08

quote:
What should be the desired output? Can you show us an example?

For example I have the following Data say for a movies table in which there is a column called Actors as shown below

Actors
-----------------------------------------------------------------------
Sam Neill,Laura Dern,Jeff Goldblum,Richard Attenborough
Harrison Ford,Kate Capshaw,Jonathan Ke Quan,Amrish Puri,Roshan Seth,Philip Tan,Dan Aykroyd
Harrison Ford,Karen Allen,John Rhys-Davies,Alfred Molina,William Hootkins
Harrison Ford,Sean Connery,Alison Doody,John Rhys-Davies,River Phoenix,Alex Hyde-White
Georgie Henley,Skandar Keynes,William Moseley,Anna Popplewell,Tilda Swinton,James McAvoy,Jim Broadbent,Kiran Shah
James Cosmo,Shane Rangi
Craig T. Nelson,Holly Hunter,Samuel L. Jackson,Jason Lee

How do i search the column for a specific actor using TSQL?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-05-06 : 06:44:18

[code]select * from table where col like '%' + @actor + '%'[/code]

EDIT: Removed trailing comma from WHERE clause

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

sam13
Starting Member

6 Posts

Posted - 2008-05-06 : 10:04:39

quote:

pootle_flump

Are you aware that this design breaks standard database design principles?

How can you say the design breaks?. You dont even know what the database is and its tables. I have specified a sample column of a table and all I want to know how you query that in sql if you have CSV values specifically for searching.

quote:

harsh_athalye

select * from table where col like '%' + @actor + ',%'

Thanks for the tip. Works like a charm.

pootle_flump

1064 Posts

Posted - 2008-05-07 : 04:46:10

quote:
Originally posted by sam13

quote:

pootle_flump

Are you aware that this design breaks standard database design principles?

How can you say the design breaks?. You dont even know what the database is and its tables. I have specified a sample column of a table and all I want to know how you query that in sql if you have CSV values specifically for searching.

Because first normal form states that all values within a column must be atomic (i.e. a single thing or value). A CSV list of values breaks this rule. There are so many reasons for not storing CSV data (relational integrity, incredibly complex SQL for even trivial data requests, unsargable where clauses - exactly as Harsh has just given you). It is also trivial to NOT break this rule. I don't need to know your tables nor what the database is for. The fact you store data like this AND need to query it is sufficient.

There are fundamental principles to relational database design. One of these is normalisation. If you don't know these principles please have a good read through this link - it will take you a little time but will save you countless hours in the future:
http://www.tonymarston.net/php-mysql/database-design.html

I know you think Harsh has helped you out and I am attacking you. From where I am standing, Harsh has just loaded the gun for you and I am trying to keep you from pointing it at your head.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources