Solved

mysql query

Posted on 2013-11-12
8
331 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 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 48

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SP converting date time to date and time separately 2 40
T-SQL: problem comparing datetime 4 81
T-SQL: Need to trim a single leading space 7 62
Creating a View from a CTE 15 49
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL several years ago, it seemed like now was a good time to update it for object-oriented PHP.  This article does that, replacing as much as possible the pr…
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

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