I have started a new job and inherited a very bad database system - they use keywords to join everything up - no primary keys - anyway!

The keywords specifying to a certain course are listed in a text field as 3, 13, 56 so to find courses that contain the keyword 6 i do a SQL for:

SELECT * FROM PostGrad WHERE keywords LIKE '6';

and I get no results - I do however know that there are loads that should come up! Am I doing something really stupid or is the database just wrong! It's an Access one if thats any help!

Hope someone can help!

Ross

SELECT * FROM PostGrad WHERE keywords LIKE '6,%' OR keywords LIKE '%,6,%' OR keywords LIKE '%,6';

Its dirty... but hopefully will do what you need

Cheers

Andy (mod)
andy@ultranerds.co.uk

---

IMPORTANT: I've now moved to ultranerds.co.uk, and the .com will no longer work!
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package (plugins total "value" $3,325 & rising, for just $350)| GLinks ULTRA Package PRO (plugins total "value" $5,625 & rising, for just $500)
Support Forum | Links SQL Plugins | DMOZ Dumps | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
Compare our different Plugin packages *new* Free CSS Templates

still doesn't work.......

i had tried something like that one already with no success :< and i try it your way....still no sucess :<

SELECT * FROM PostGrad WHERE FIND_IN_SET('6',keywords)

chmod


ooops! just read its an access database find_in_set is a mysql function, sorry.

This should work...

SELECT * FROM PostGrad WHERE keywords LIKE '%6%'

That would grab 16, 61, etc. Andy had it close:

SELECT * FROM PostGrad WHERE keywords LIKE '6,%' OR keywords LIKE '%,6,%' OR keywords LIKE '%,6';

but was missing the case where keywords contains only 6, so:

SELECT * FROM PostGrad WHERE keywords LIKE '6,%' OR keywords LIKE '%,6,%' OR keywords LIKE '%,6' OR keywords like '6'

should do the trick.

Cheers,

Alex

--
Gossamer Threads Inc.

Ah. I understood the question differently. I thought Ross was asking to find any row that contained a 6, regardless of its position.

I copied that query in exactly and still the only one it would pick up was the final condition where the 6 was in on its own.

I am now using

SELECT * FROM PostGraduate WHERE keywords LIKE '6,#' OR keywords LIKE '#,6' OR keywords LIKE '#, 6,#' OR keywords LIKE '6';

and it is picking up the ones where 6 is the last keyword of the list (so I imagine the '#,6'_ but still that is only getting it when the entry is two long, such as 4,6 not when there is something like 5,78,65,6. I am pulling my hair out here as I think all of the yhelp you have given me is great and should work but for some reason it isn't and the only thing I can see to blame is the database....which I had nothing to do with I can say

anyone got any more idea's?