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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Suggested Solutions

CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
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…
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…

734 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