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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
powershell and sql server - alerting 7 87
Exchange 2010 - Best practice MDB Data size 8 82
SSRS troubles 4 50
T-SQL Default value in Select? 5 26
I guess that all of us know that caching the data usually increase the performance, but I worried if all of us are aware about the risk that caching the data provides and how to minimize this.  That’s the reason why I decided to write this short art…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
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…

772 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