Solved

mysql query

Posted on 2013-11-12
8
314 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
  • 5
  • 3
8 Comments
 
LVL 48

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 48

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
 
LVL 48

Expert Comment

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

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

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 48

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 48

Expert Comment

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

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

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…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

705 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

23 Experts available now in Live!

Get 1:1 Help Now