Help with simple MySQL Query

Bruce Wedding · 02-17-2013, 12:17 PM

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:

Dan Grossman · 02-17-2013, 01:16 PM

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

Bruce Wedding · 02-17-2013, 03:01 PM

That works, Dan. Thanks a lot.

FirstSocialApps · 02-17-2013, 06:11 PM

Quote:

Originally Posted by Dan Grossman

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?

KirkMcD · 02-19-2013, 05:24 AM

For that query, no. In fact it is wrong.
instr returns the position of the substring in the main string.

Dan Grossman · 02-19-2013, 10:03 AM

Quote:

Originally Posted by KirkMcD

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.

02-17-2013, 12:17 PM	#1
Bruce Wedding The Reality Check War Room Member Join Date: Apr 2006 Location: Cancun, Quintana Roo, MX Posts: 3,958 Thanks: 566 Thanked 935 Times in 417 Posts Social Networking Contact Info	Help with simple MySQL Query 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:
	Click Me for a FREE Drink at The JV Bar — Where Marketers Meet

02-17-2013, 01:16 PM	#2
Dan Grossman White Hat Marketer War Room Member Join Date: Jan 2007 Location: Montgomeryville, PA Posts: 1,342 Thanks: 71 Thanked 598 Times in 193 Posts Social Networking	Re: Help with simple MySQL Query 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%'
	17-29% of paid ad clicks are fraudulent. Get protected with Improvely, your online marketing dashboard — Track traffic & conversions, detect click fraud, cloak affiliate links, split test landing pages & more

02-17-2013, 03:01 PM	#3
Bruce Wedding The Reality Check War Room Member Join Date: Apr 2006 Location: Cancun, Quintana Roo, MX Posts: 3,958 Thanks: 566 Thanked 935 Times in 417 Posts Social Networking Contact Info	Re: Help with simple MySQL Query That works, Dan. Thanks a lot.
	Click Me for a FREE Drink at The JV Bar — Where Marketers Meet

02-19-2013, 05:24 AM	#5
KirkMcD Senior Warrior Member War Room Member Join Date: Apr 2006 Location: , , USA. Posts: 2,872 Thanks: 55 Thanked 227 Times in 210 Posts	Re: Help with simple MySQL Query For that query, no. In fact it is wrong. instr returns the position of the substring in the main string.