Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 122
  • Last Modified:

MySQL Oddity

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
Richard Korts
Asked:
Richard Korts
  • 5
  • 4
1 Solution
 
GaryCommented:
Are you sure you don't have any spaces in the Favourites cell?
0
 
GaryCommented:
Does it work with this

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

Open in new window

0
 
Richard KortsAuthor Commented:
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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
Richard KortsAuthor Commented:
Is cdesc a reserved word in MySQL?
0
 
GaryCommented:
No.

Can you do a dump of the table and attach here, there could be invisible characters like a CR in the field
0
 
Richard KortsAuthor Commented:
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
 
Dave BaldwinFixer of ProblemsCommented:
Yes, it does.  Click on the Export tab.
0
 
Richard KortsAuthor Commented:
OK, attached.
part-codes.sql
0
 
Richard KortsAuthor Commented:
FYI, I solved my immediate need using like & the full string followed with %

Crazy!!
0
 
GaryCommented:
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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now