Multiple results in one column MS SQL 2000

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

General SQL Server Forums

New to SQL Server Programming

Multiple results in one column MS SQL 2000

Author

Topic

Sonofmarz
Starting Member

3 Posts

Posted - 2008-06-11 : 10:55:56

Hi all,

I'm totally new and been reading the forums to help me and I think I've done ok so far getting the results I need from basic SQL selects and stuff but now I seem to have hit a wall and I realize I need help from someone who really knows their SQL.

I'm using Microsoft SQL 2000.

I have a database with columns

Product_ID
Product_Name
Product_Association

The Product_ID column is the unique ID key. Product_Name is the name of the product. And Product_Assocation represents search terms.

The column Product_Assocation has for example the values

nature*leaves*leaf*tree*trees*abstract*green

for one product and

food*dinner*table*eat*eating*restaurant*cuisine*plate*dinner*knife

for another.

I need to do 2 things basically.

1. How do I select the product based on ProductID and display each word independently in a recordset?

For example how can I Select from Table where Associate = 'each word and list them' for Product_ID = '1'

2. I have to allow the user to "search" for a word in the column so I need to create an SQL statement that goes someting like

Select * from Table where searchresult = 'search word for example tree which is a word seperated by one of the * in the Product_Association column' and that the recordset returns the table row.

I am sure these both probably sound like silly and simple questions and if they are I am sorry guys. I am trying my best. I am a newbie and no point pretending otherwise. Hopefully someone with greater SQL knowledge out there can assist me because I have tried all I know but can't seem to make any headway.

I am using ASP not .net or php if that makes any difference? I'm not sure that it does as I think it is about the SQL Select statement and how to construct them?

Thanks for any help. Its really appreciated.

Kind regards, Sam

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-11 : 11:42:17

1. For this you might require a table valued udf which takes the string of productid values seperated by * and returns a table containing those values. You could serach for a function called fnParseValues within this forum itslef.

2. i think you can achieve this by using something like

SELECT * FROM Table WHERE '*'+ @SearchWord+ '*' LIKE '%*' + Product_Association + '*%'

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-06-11 : 12:13:46

quote:

1. For this you might require a table valued udf which takes the string of productid values seperated by * and returns a table containing those values. You could serach for a function called fnParseValues within this forum itslef.

I believe this article is what visakh is referring to:
http://www.sqlteam.com/article/parsing-csv-values-into-multiple-rows

quote:

2. i think you can achieve this by using something like

SELECT * FROM Table WHERE '*'+ @SearchWord+ '*' LIKE '%*' + Product_Association + '*%'

SELECT * FROM Table WHERE '*'+Product_Association+'*' LIKE '%*'+@SearchWord+'*%'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-11 : 12:29:44

quote:
Originally posted by jdaman

quote:

1. For this you might require a table valued udf which takes the string of productid values seperated by * and returns a table containing those values. You could serach for a function called fnParseValues within this forum itslef.

I believe this article is what visakh is referring to:
http://www.sqlteam.com/article/parsing-csv-values-into-multiple-rows

quote:

2. i think you can achieve this by using something like

SELECT * FROM Table WHERE '*'+ @SearchWord+ '*' LIKE '%*' + Product_Association + '*%'

SELECT * FROM Table WHERE '*'+Product_Association+'*' LIKE '%*'+@SearchWord+'*%'

Thanks for the catch jdaman. I had it just the opposite way

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-06-11 : 12:33:54

For the amount of help you dish out you're allowed a few typos.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-11 : 12:36:17

quote:
Originally posted by jdaman

For the amount of help you dish out you're allowed a few typos.

Thank you so much

Sonofmarz
Starting Member

3 Posts

Posted - 2008-06-11 : 16:24:27

Hi guys,

many thanks for all your input and help. jdaman, visakh16...you guys are great!

I tried this string.

SELECT * FROM TABLE WHERE '*' + Product_Association + '*' LIKE '%*' + @searchword + '*%')

with real values.

SQL states "Must declare the variable @and-the-search-word-i-used"

Any ideas?

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-06-11 : 16:40:19

DECLARE @searchword varchar(25)
SET @searchword = YOUR SEARCH WORD GOES HERE

SELECT * FROM TABLE WHERE '*' + Product_Association + '*' LIKE '%*' + @searchword + '*%')

Sonofmarz
Starting Member

3 Posts

Posted - 2008-06-11 : 17:01:48

jdaman what can i say? thanks seems too little and anything else seems a sbit stupid so i'll just say thank you for everything man. thank you

Sam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources