SQL handle Punctuation?

Posted on 2004-10-15
Last Modified: 2012-06-27
Hey there,

I have a question about handling punctuation in SQL..

I want the SQL to be able to handle phrases such as "Dean's List" (including the apostrophe).

My current sql string is:

sql = new String("select distinct DIVISION_NAME from people_db.V_DEPARTMENT where DEPARTMENT_NAME="' + someDeptString()"' and  
sql = sql +"order by DIVISION_NAME";

I keep getting this error:

EvaluateExpression failed: <com.webobjects.jdbcadaptor.OraclePlugIn$OracleExpression: "select distinct DIVISION_NAME from people_db.V_DEPARTMENT where DEPARTMENT_NAME= 'Dean's Office' and INSTITUTION_LABEL= 'HR' ">: Next exception:SQL State:42000 -- error code: 933 -- msg: ORA-00933: SQL command not properly ended

How can I handle the apostrophes properly?

Any advice or guidance would be greatly appreciated!

Question by:jmc430
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
  • 2
  • 2

Expert Comment

ID: 12325662
When coding SQL, you must enter a pair of single quotes whenever you wish to place a single quote within a character string.  In your case, since you are pulling your string from the database to execute dynamically, you must use the REPLACE function to replace any occurrence of one single-quote with a pair of single-quotes.

Here's how I would modify your statement:

sql = new String("select distinct DIVISION_NAME from people_db.V_DEPARTMENT where DEPARTMENT_NAME="' + Replace(someDeptString(), chr(39), chr(39)+chr(39)) + "' and INSTITUTION_LABEL="'+Replace(someInstitutionString(), chr(39), chr(39)+chr(39)) +"'");
sql = sql +"order by DIVISION_NAME";

I hope this solves your problem.

-- Brian

LVL 18

Expert Comment

ID: 12331437
Setting Quoted_Identifier off can deal with your problem here:




Author Comment

ID: 12338045
hi brian ..

am i incorrectly implementing this?

the sql select statement you suggested gave me 6 same compilation errors:
(i cut and paste and just replaced my variable names)

cannot resolve symbol: method chr (int)

am i doing something wrong?

thanks so much for your help!


Accepted Solution

stewartwb earned 125 total points
ID: 12339967

I'm assuming you are using Oracle's PL/SQL variant of the SQL.  From the online documentation:

Oracle/PLSQL: Chr Function
The chr function is the opposite of the ascii function.  It returns the character based on the ASCII NUMBER code.

The syntax for the chr function is:
chr (number_code)

number_code is the NUMBER code used to retrieve the character.

For example:

chr (116); would return 't'.
chr (84); would return 'T'

If you continue to have trouble with the chr() function, try specifying the apostrophes as a string. When formatted this way, your statement would be:

sql = new String("select distinct DIVISION_NAME from people_db.V_DEPARTMENT where DEPARTMENT_NAME="' + Replace(someDeptString(), '''', '''''') + "' and INSTITUTION_LABEL="'+Replace(someInstitutionString(), '''', '''''') +"'");
sql = sql +"order by DIVISION_NAME";

The second parameter of the Replace method has four apostrophes, the third has six.

Finally, are you sure you should be using double-quotes around your strings?  You might try the following instead:

sql = new String('select distinct DIVISION_NAME from people_db.V_DEPARTMENT where DEPARTMENT_NAME=''' + Replace(someDeptString(), '''', '''''') + ''' and INSTITUTION_LABEL='''+Replace(someInstitutionString(), '''', '''''') +'''');
sql = sql + ' order by DIVISION_NAME ';

And don't forget to add a space before the  ' order by DIVISION_NAME'  clause at the end.

In T-SQL, I ran the following script to test my code.  Here is the script and the output:

declare @str1 varchar(30), @str2 varchar(30)
set @str1 = 'Dick''s Sports'
set @str2 = 'Bob''s Garage'

select 'select distinct DIVISION_NAME from people_db.V_DEPARTMENT where DEPARTMENT_NAME=''' + Replace(@str1, '''', '''''') + ''' and INSTITUTION_LABEL='''+Replace(@str2, '''', '''''') +''''

select distinct DIVISION_NAME from people_db.V_DEPARTMENT where DEPARTMENT_NAME='Dick''s Sports' and INSTITUTION_LABEL='Bob''s Garage'

I hope this helps.
-- Brian

Author Comment

ID: 12506173
apologies for the delay .. i thought i granted the points earlier.

thanks for your help!!


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

Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

617 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