Gossamer Forum
Home : General : Databases and SQL :

using LIKE when searching through number strings

Quote Reply
using LIKE when searching through number strings
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
Quote Reply
Re: [rossbruniges] using LIKE when searching through number strings In reply to
SELECT * FROM PostGrad WHERE keywords LIKE '6,%' OR keywords LIKE '%,6,%' OR keywords LIKE '%,6';

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

Cheers

Andy (mod)
andy@ultranerds.co.uk
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
Quote Reply
Re: [Andy] using LIKE when searching through number strings In reply to
still doesn't work.......

i had tried something like that one already with no success :< and i try it your way....still no sucess :<
Quote Reply
Re: [rossbruniges] using LIKE when searching through number strings In reply to
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.

Last edited by:

chmod: Feb 8, 2005, 2:18 PM
Quote Reply
Re: [rossbruniges] using LIKE when searching through number strings In reply to
This should work...

SELECT * FROM PostGrad WHERE keywords LIKE '%6%'
Quote Reply
Re: [Gmail] using LIKE when searching through number strings In reply to
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.
Quote Reply
Re: [Alex] using LIKE when searching through number strings In reply to
Ah. I understood the question differently. I thought Ross was asking to find any row that contained a 6, regardless of its position.
Quote Reply
Re: [Alex] using LIKE when searching through number strings In reply to
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 Tongue

anyone got any more idea's?