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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Act 2012, need script to share a database? 17 113
ODBC Informix Driver 11 92
database design books 12 79
Invalid Identifier Error 3 65
Introduction: I have seen many questions on EE and elsewhere, asking about how to find either gaps in lists of numbers (id field, usually) ranges of values or dates overlapping date ranges combined date ranges I thought it would be a good …
Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
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…

706 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now