View Full Version : search within field contents

02-20-2009, 09:42 AM
data, one column


so the query should return first two rows

could do like:

where col like '%|45|%'

not sure this is optimal, any suggestion ?

02-20-2009, 10:19 AM
not sure this is optimal, any suggestion ?
Surely this is not optimal as you think! See our mod's article Separating lists and normalizing data (http://www.guelphdad.wefixtech.co.uk/sqlhelp/lists.shtml)

02-20-2009, 10:34 AM
yeah, know that, if you look at this thread

you will get the idea.

The data above is in fact like this, and col2 sole intention is for use for this search:

col1 col2
233 |root|23|345|23|45|67|8909|233|
58 |root|1|67|45|58|
45234 |root|15|36|46|58|45234|

where col1 is picked item from tree structure, and col2 entire path back to root for that item. Now, if tree is forinstance like

Animals--> mammals(45)-> whales
--> mammals(45)-> cows

and want to search for mammals, then this makes sence, without normalizing ?

where col like '%|45|%'

02-20-2009, 03:22 PM
normalize the data. your query works but does not scale well and shouldn't be used for real applications/data.