Solved

mysql query

Posted on 2013-11-12
8
321 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

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

 
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!

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle SQL - Query help 7 55
Character matching different date formats for dates between 6 46
Query Syntax 17 36
SQL Syntax: How to force case sensitive query? 2 30
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

809 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