Solved

mysql query

Posted on 2013-11-12
8
334 Views
Last Modified: 2013-11-13
Following is the code I am using to read a row from a mysql table.  The name that is in the  jTextField_permName text field is 'Schaff's Angus Ranch'.  I get an error because of the tick mark in first part of the name.  It partially says:

server version for the right syntax to use near 'S ANGUS RANCH'' at line 1

How do I allow the use of punctuation marks in the data?  THANKS!
0
Comment
Question by:sargent240
[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
  • 3
8 Comments
 
LVL 49

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 39643668
To read a recode for name "Schaff's Angus Ranch" use double quotes, OR
, "escape" the single quote like the folowing:
    CREATE TABLE YourTable
    	(`FullName` varchar(80))
    ;
    	
    INSERT INTO YourTable
    	(`FullName`)
    VALUES
    	('Schaff''s Angus Ranch')
    ;

**Query 1**:

    select
    *
    from YourTable
    where FullName = "Schaff's Angus Ranch"
    

**[Results][2]**:
    
    |             FULLNAME |
    |----------------------|
    | Schaff's Angus Ranch |


**Query 2**:

    select
    *
    from YourTable
    where FullName = 'Schaff\'s Angus Ranch'
    

**[Results][3]**:
    
    |             FULLNAME |
    |----------------------|
    | Schaff's Angus Ranch |



  [1]: http://sqlfiddle.com/#!9/bb913/1

Open in new window

0
 

Author Comment

by:sargent240
ID: 39643702
So whenever someone enters a name or other data do I need to search for any punctuation and add the single quotes.  And then go through the existing data table, look for punctuation and add single quotes?
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39643711
Look carefully at line 8 of my comment.

During data entry a name that contains a single quote needs to be handled properly to ensure it does get written accurately into the table.

Once that name is in the table it only contains the wanted name, in this case:
Schaff's Angus Ranch

When searching for such a name you have a choice:
use double quotes, or
"escape" the single quote

How are you writing data into tables? e.g. through PHP
in which case you might want to raise a PHP question on best practices for handling such data
0
Do you have a plan for Continuity?

It's inevitable. People leave organizations creating a gap in your service. That's where Percona comes in.

See how Pepper.com relies on Percona to:
-Manage their database
-Guarantee data safety and protection
-Provide database expertise that is available for any situation

 
LVL 49

Expert Comment

by:PortletPaul
ID: 39643715
if PHP, this article may be helpful: Quotation Marks in PHP (Ray Paseur)
0
 

Author Comment

by:sargent240
ID: 39643725
I am using Java.  I have a data table with about 15,000 names in it and if I understand, I will have to go through that data table, search for punctuation and change the field to match the one you posted in line 8 above is that correct?  When the operator clicks the save button for example and I prepare to save the data in the mysql table if I understand correctly I will need to try and detect punctuation and add a single quote.  If that is the case, what is the best way to detect punctuation in a string?
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39643750
The same sort of advice applies - you may want to raise a java question on best practices for handling strings with embedded single quotes.

>>I will have to go through that data table, search for punctuation and change the field to match the one you posted in line 8 above is that correct?

Not sure why you think that is necessary. What happens if you run one of these (they are equivalents):
select
       FullName
from YourTable
where FullName like "%'%"
;

select
       FullName
from YourTable
where FullName like '%\'%'
;

Open in new window

Is the data that you see from this accurate or inaccurate? (remember we can't "see" your results).

If it's accurate then no action, if inaccurate then action.

i.e. if
               Schaff's Angus Ranch
is indeed
               Schaff's Angus Ranch
then no action should be required
0
 

Author Closing Comment

by:sargent240
ID: 39646201
Thanks!  I test a couple things and understand the process now.  Appreciate you going the little extra mile.  Cheers.
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39646334
No problem, thanks for the grading. Cheers, Paul
0

Featured Post

Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

Question has a verified solution.

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

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 this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

623 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