Help with simple MySQL Query

Profile picture of the author Bruce Wedding by
I have very little SQL experience and have a problem with a query that seems relatively easy. It's returning a null set and SQL Explain says it's an impossible where.

Here's the query:
SELECT 'password' FROM `album` WHERE `uid`=124 AND instr('title', '21.01')

Here's the DB structure:


Here's the actual record with the matching uid and title:
#programming #mysql #query #simple

  • Profile picture of the author Dan Grossman
    INSTR('title', '21.01') looks for the string '21.01' in the string 'title'. That substring does not appear, so the query can never match any rows so none are even checked.

    What you meant to write was INSTR(title, '21.01') which looks for the string '21.01' in the column named title.

    I've never seen someone use INSTR though. Most people would write
    Code:
    WHERE uid = 124 AND title LIKE '%21.01%'
  • Profile picture of the author Bruce Wedding
    That works, Dan. Thanks a lot.
  • Profile picture of the author FirstSocialApps
    Originally Posted by Dan Grossman View Post

    I've never seen someone use INSTR though. Most people would write [code]WHERE uid = 124 AND title LIKE '%21.01%'
    Wildcards are the way I have always done a search like this as well. Is there some advantage to using INSTR?
  • Profile picture of the author KirkMcD
    For that query, no. In fact it is wrong.
    instr returns the position of the substring in the main string.
  • Profile picture of the author Dan Grossman
    Originally Posted by KirkMcD View Post

    For that query, no. In fact it is wrong.
    instr returns the position of the substring in the main string.
    It's not in fact wrong at all. MySQL needs a boolean to evaluate the WHERE clause of the query, and coercing the integer return value of INSTR will have the desired result. INSTR returns 0 if the substring doesn't appear, which is coerced to boolean false. If the substring does appear, its index would be a positive integer, and any positive integer is coerced to boolean true.

Next Topics on Trending Feed