Solved

mysql query

Posted on 2013-11-12
8
327 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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
T-SQL: Do I need CLUSTERED here? 13 59
Coldfusion/Mysql page error related to dynamic table creation. 9 33
Procedure syntax 5 41
SQL Select in Access 2003 3 27
Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
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…

830 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