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 7 of 7
  1. #1
    Regular Coder
    Join Date
    Oct 2009
    Posts
    440
    Thanks
    7
    Thanked 3 Times in 3 Posts

    SELECT WHERE LIKE not working if string to compare is longer than whats in DB

    Code:
    SELECT * FROM `table` WHERE LOWER(field1) LIKE '%string1%' AND LOWER(field2) LIKE 'abc d%'  ORDER BY `road`
    The issue I think is only with the last part of the query...

    LOWER(field2) LIKE 'abc d%' ORDER BY `field1`

    If the string to search for... 'abc d' is longer than what exists in the DB field field2 then nothing is returned. If the string to search was just 'abc' then it does return results as expected.

    How would I query so it returns results if the string to search and the field2 is LIKE even if there is extra characters after the string to search?

  • #2
    Master Coder felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, Australia
    Posts
    6,642
    Thanks
    0
    Thanked 649 Times in 639 Posts
    field3 is not lLIKE if the value to compare is longer than the database field.

    The % in the value can have zero or more characters substituted for it but 'abc d' followed by nothing or anything will never equal a four character field.

    For a value to be LIKE the value including the % must not be longer than the length of the field you are comparing it to.

    If the field contains 'abc ' then the follwinf like strings will match it:

    'abc%'
    'ab%'
    'a%'
    '%bc '
    '%c '
    '% '
    '%a%'
    '%b%'
    '%c%'
    '% %'
    'abc_'
    'ab_ '
    'a_c '
    '_bc '
    '__c '
    'a__ '
    '_b_ '

    and so on - none of which are more than four characters long.

    _ represents any one character
    % represents any number of any character
    anything else must appear as specified
    Stephen
    Learn Modern JavaScript - http://javascriptexample.net/
    Helping others to solve their computer problem at http://www.felgall.com/

    Don't forget to start your JavaScript code with "use strict"; which makes it easier to find errors in your code.

  • #3
    Regular Coder
    Join Date
    Oct 2009
    Posts
    440
    Thanks
    7
    Thanked 3 Times in 3 Posts
    just to clarify... field2 might have 'abc' in which case the row that this is in would be returned. Field2 does not have just three characters this was just an example, the text could be anything from 3 to 8 characters in length.

  • #4
    Regular Coder
    Join Date
    Oct 2009
    Posts
    440
    Thanks
    7
    Thanked 3 Times in 3 Posts
    i even swapped the field and string in the query...

    'abc d%' LIKE LOWER(field2) ORDER BY `field1`

    but this still did not work.

  • #5
    Master Coder felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, Australia
    Posts
    6,642
    Thanks
    0
    Thanked 649 Times in 639 Posts
    LIKE 'abc d%' will match any field that starts with 'abc d'

    It will not match any field that does not contain those first five characters.
    Stephen
    Learn Modern JavaScript - http://javascriptexample.net/
    Helping others to solve their computer problem at http://www.felgall.com/

    Don't forget to start your JavaScript code with "use strict"; which makes it easier to find errors in your code.

  • #6
    Regular Coder
    Join Date
    Oct 2009
    Posts
    440
    Thanks
    7
    Thanked 3 Times in 3 Posts
    How do we reverse the function so it checks the smaller being in the DB (field2) against the search term string.

    or is there another way.


    Just rethinking about the fact that you can not LIKE a longer string to a smaller one, can you do a check to see if the longer is like the smaller... but append to the smaller (field2) content before the check is done?

    Code:
    '%abc d%' LIKE LOWER(field2+'            ') ORDER BY `field1`
    Last edited by needsomehelp; 03-13-2014 at 07:51 AM.

  • #7
    Regular Coder
    Join Date
    Oct 2009
    Posts
    440
    Thanks
    7
    Thanked 3 Times in 3 Posts
    I just tried this...

    Code:
    '%abc d%' LIKE CONCAT(field2,'              ') ORDER BY `field1`
    but this did not work, or I am doing it wrong...

    am I close ?


  •  

    Posting Permissions

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