Finding a Value In a Database Table Multivalued Column

Question

I have the following table

search_redirect
sr_id | sr_keyword | sr_redirect | sr_synonyms
------------------------------------------------------
1     | chair      | www.url.com | recliner,seat,stool
2     | couch      | www.url.com | sofa,divan
3     | dresser    | www.url.com | chest,drawers

When someone uses the search feature on my site, I want to be able to compare what they search to the list of synonyms. My attempt at a solution was this:

SELECT sr_redirect
FROM search_redirect
WHERE '#url.q#' IN (sr_redirect)

Unfortunately, while this is a completely legal move, it doesn't do what I thought it would do. My next attempt was to try and add single quotes to my values in the sr_synonyms, but that also did not yield successful results. Its difficult to word this issue so I was unable to find any help on this situation. Any advise I can get on this would be most appreciated. I am using ColdFusion 9 server-side scripting with SQL Server 2008.

are you trying to see if what they search for is in the sr_redirect column? — w0051977, Aug 31 '12 at 20:02

ajon · Accepted Answer · 2012-08-31T20:05:35.193

0

To maintain a pure sql solution I would change your table into 2 tables.

The first could have :

search_redirect

sr_id | sr_keyword | sr_redirect

The second could have

search_keyword_to_synonym

sr_id | sr_synonym

Then only have 1 synonym per row in the second table. Your solution could then be:

SELECT sr_redirect FROM search_redirect
INNER JOIN search_keyword_to_synonym
ON search_redirect.sr_id=search_keyword_to_synonym.sr_id
WHERE search_redirect.sr_keyword=whatever_input_you_have.

edited Aug 31 '12 at 20:05

answered Aug 31 '12 at 19:59

ajon

7,868
11
48
86

this could very well be the most obvious solution in the history of solutions. I feel thick headed for even attempting to store a list in a table column. – ckxcore Aug 31 '12 at 20:13

score 0 · Answer 2 · answered Aug 31 '12 at 20:02

First of all:

Never, ever put more than one information into one field, if you want to access them separately. Never

That said, this can easily be rectified: Create a table "Synonyms" like

synonym | sr_id
---------------
recliner| 1
seat    | 1
stool   | 1
sofa    | 2
divan   | 2

and be done with it.

score 0 · Answer 3 · answered Aug 31 '12 at 20:03

0

you may use a similar syntax to this:

SELECT sr_redirect
FROM search_redirect
WHERE substr('#url.q#',sr_synonyms) > 0

i agree with others however - re-normalize

answered Aug 31 '12 at 20:03

Randy

16,480
1
37
55

Finding a Value In a Database Table Multivalued Column

3 Answers3

sr_id | sr_keyword | sr_redirect

sr_id | sr_synonym