Solved

MySQL Oddity

Posted on 2014-12-02
10
101 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
  • 5
  • 4
10 Comments
 
LVL 58

Expert Comment

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

Expert Comment

by:Gary
Comment Utility
Does it work with this

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

Open in new window

0
 

Author Comment

by:Richard Korts
Comment Utility
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
 

Author Comment

by:Richard Korts
Comment Utility
Is cdesc a reserved word in MySQL?
0
 
LVL 58

Expert Comment

by:Gary
Comment Utility
No.

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

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 

Author Comment

by:Richard Korts
Comment Utility
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 82

Expert Comment

by:Dave Baldwin
Comment Utility
Yes, it does.  Click on the Export tab.
0
 

Author Comment

by:Richard Korts
Comment Utility
OK, attached.
part-codes.sql
0
 

Author Comment

by:Richard Korts
Comment Utility
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
Comment Utility
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
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…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

744 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now