Results 1 to 5 of 5

Thread: Select records from database

  1. #1
    watersniper's Avatar
    Join Date
    Dec 2012
    Gender
    Posts
    113
    Thanks
    20
    Thanked 13 Times in 12 Posts


    Downloads
    16
    Uploads
    1
    Mentioned
    8 Post(s)
    Time Online
    1 d 23 h 14 m
    Avg. Time Online
    1 m
    Rep Power
    5

    Select records from database

    This isn't neopets related, but since you guys are all brilliant programmers, maybe you have some experience and can shed some light?

    I'm using enterprise javabeans and have a database which uses EJB-QL (which doesn't seem to recognize REGEXP, otherwise I could use .\\b).
    I need to select records containing keywords, eg. "pin". And don't want it to return things "lynchpin" or "pineapple".

    Any idea how to do this?

    Edit: A clarification on the question --
    Essentially my column consists of chunks of text, and I'm looking for the records whose chunk of text contain a keyword.
    Normally it would be this way:
    SELECT e.column FROM TABLE e WHERE e.column LIKE '%pin%'
    However, this will cause records containing text with the terms 'lynchpin' and 'pineapple' to be chosen as well.

    So I'm looking for something that will bound the keyword, such as in regex: ".\bpin\b."

    Otherwise I will have to select all the records then run the text through a Pattern/Matcher to see if it returns false, which is inefficient.
    Last edited by watersniper; 07-04-2013 at 12:30 AM.

  2. #2
    Saiyan Race





    Infamous Joe's Avatar
    Join Date
    Dec 2011
    Gender
    Location
    Hyperbolic Time Chamber
    Age
    26
    Posts
    9,754
    Thanks
    2,780
    Thanked 16,239 Times in 4,238 Posts


    Downloads
    23
    Uploads
    36
    Mentioned
    2985 Post(s)
    Time Online
    293 d 17 h 55 m
    Avg. Time Online
    3 h 44 m
    Rep Power
    10
    Gamer IDs

    Gamertag: Infam0us Joe Steam ID: infamousjoe
    No experience with the environment you are working with but this should still be of use:

    http://stackoverflow.com/questions/1...pecific-result

    Check the solution given to that question.

    @watersniper

  3. #3
    watersniper's Avatar
    Join Date
    Dec 2012
    Gender
    Posts
    113
    Thanks
    20
    Thanked 13 Times in 12 Posts


    Downloads
    16
    Uploads
    1
    Mentioned
    8 Post(s)
    Time Online
    1 d 23 h 14 m
    Avg. Time Online
    1 m
    Rep Power
    5
    Sorry @Infamous Joe I don't really get how the example you suggested relates.

    Perhaps I should clarify my question.

  4. #4
    Little Brother


    Zachafer's Avatar
    Join Date
    Dec 2011
    Gender
    Posts
    1,206
    Thanks
    723
    Thanked 1,392 Times in 651 Posts


    Downloads
    81
    Uploads
    17
    Mentioned
    497 Post(s)
    Time Online
    21 d 17 h 32 m
    Avg. Time Online
    16 m
    Rep Power
    14

  5. #5
    watersniper's Avatar
    Join Date
    Dec 2012
    Gender
    Posts
    113
    Thanks
    20
    Thanked 13 Times in 12 Posts


    Downloads
    16
    Uploads
    1
    Mentioned
    8 Post(s)
    Time Online
    1 d 23 h 14 m
    Avg. Time Online
    1 m
    Rep Power
    5
    Thanks, Zachafer, but EJBQL doesn't support that either. Your first solution would ignore any text such as these: ' pin,' , ' pin!', '(pin ', ' pin)' etc which are all valid syntactically in my chunks of text. In frustration I moved my database to mysql so I could use REGEXP.

    In case anyone ever stumbles upon this thread with the same problem, one workaround is to enter all the allowed values in another table, then you could write
    SELECT DISTINCT e.column FROM FirstTable e, ValueTable v WHERE e.column LIKE v.value

    Sucks if your keyword constantly changes though.

Tags for this Thread

Posting Permissions

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