Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,963
    Thanks
    120
    Thanked 76 Times in 76 Posts

    search within field contents

    data, one column
    Code:
    |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:
    Code:
    where col like '%|45|%'
    not sure this is optimal, any suggestion ?

  • #2
    Supreme Master coder! abduraooft's Avatar
    Join Date
    Mar 2007
    Location
    N/A
    Posts
    14,854
    Thanks
    160
    Thanked 2,223 Times in 2,210 Posts
    Blog Entries
    1
    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
    The Dream is not what you see in sleep; Dream is the thing which doesn't let you sleep. --(Dr. APJ. Abdul Kalam)

  • #3
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,963
    Thanks
    120
    Thanked 76 Times in 76 Posts
    yeah, know that, if you look at this thread
    tree data structure

    you will get the idea.

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

    Code:
    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

    Code:
    Animals--> mammals(45)-> whales
           --> mammals(45)-> cows
    and want to search for mammals, then this makes sence, without normalizing ?

    where col like '%|45|%'
    Last edited by BubikolRamios; 02-20-2009 at 10:12 AM.

  • #4
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    normalize the data. your query works but does not scale well and shouldn't be used for real applications/data.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •