Solved

MySQL Oddity

Posted on 2014-12-02
10
112 Views
Last Modified: 2014-12-11
See attached. The tables is small contains 25 rows.

If I do this query: "SELECT * from part_codes where cdesc = 'Favorites'" I get zero rows.

If I do "SELECT * from part_codes where cdesc like  'Favorites%'" I get the expected result.

Why can't it do something as simple as this?
part-codes-table.jpg
pc-select-equal.jpg
pc-select-like.jpg
0
Comment
Question by:Richard Korts
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
10 Comments
 
LVL 58

Expert Comment

by:Gary
ID: 40477592
Are you sure you don't have any spaces in the Favourites cell?
0
 
LVL 58

Expert Comment

by:Gary
ID: 40477593
Does it work with this

"SELECT * from part_codes where TRIM(cdesc) = 'Favorites'" 

Open in new window

0
 

Author Comment

by:Richard Korts
ID: 40477597
It DOES NOT work with this:  SELECT * from part_codes where TRIM(cdesc) = 'Favorites'

Returns zero rows.

If there are spaces, they are at the trailing end; I have built HUNDREDS of SQL like this, never an issue.

I can go with the like & put a % at the end but I can't understand why it doesn't work in such a SIMPLE case.
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

Author Comment

by:Richard Korts
ID: 40477604
Is cdesc a reserved word in MySQL?
0
 
LVL 58

Expert Comment

by:Gary
ID: 40477605
No.

Can you do a dump of the table and attach here, there could be invisible characters like a CR in the field
0
 

Author Comment

by:Richard Korts
ID: 40477620
I can;t see how to do a dump, it doesn't seem to give that option in phpMySQL. See attached.
phpmysql-ops.jpg
0
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 40477627
Yes, it does.  Click on the Export tab.
0
 

Author Comment

by:Richard Korts
ID: 40477639
OK, attached.
part-codes.sql
0
 

Author Comment

by:Richard Korts
ID: 40477642
FYI, I solved my immediate need using like & the full string followed with %

Crazy!!
0
 
LVL 58

Accepted Solution

by:
Gary earned 500 total points
ID: 40477644
All your fields have a carriage return in the value
So a slight amendment to my earlier sql

"SELECT * from part_codes where TRIM(TRAILING '\n' FROM cdesc) = 'Favorites'" 

Open in new window


Though better to remove it before inserting it into the db
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction In this installment of my SQL tidbits, I will be looking at parsing Extensible Markup Language (XML) directly passed as string parameters to MySQL 5.1.5 or higher. These would be instances where LOAD_FILE (http://dev.mysql.com/doc/refm…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question