search within field contents

[BubikolRamios]

data, one column

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


so the query should return first two rows

could do like:

where col like '%|45|%'


not sure this is optimal, any suggestion ?

[abduraooft]

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)

[BubikolRamios]

yeah, know that, if you look at this thread
http://www.codingforums.com/showthread.php?t=157051&highlight=tree

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|%'

[guelphdad]

normalize the data. your query works but does not scale well and shouldn't be used for real applications/data.