Solved

MySQL Oddity

Posted on 2014-12-02
10
115 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

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

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

I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

739 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