Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL handle Punctuation?

Posted on 2004-10-15
5
Medium Priority
?
1,501 Views
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  
                         INSTITUTION_LABEL="'+someInstitutionString()+"'");
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!

Thanks,
Jamie
0
Comment
Question by:jmc430
  • 2
  • 2
5 Comments
 
LVL 1

Expert Comment

by:stewartwb
ID: 12325662
Jamie,
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

0
 
LVL 18

Expert Comment

by:tusharashah
ID: 12331437
Setting Quoted_Identifier off can deal with your problem here:
------------------------------------------------------------------------
SET QUOTED_IDENTIFIER OFF
GO

and

SET QUOTED_IDENTIFIER ON
GO
------------------------------------------------------------------------

-tushar
0
 

Author Comment

by:jmc430
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)

Error:
cannot resolve symbol: method chr (int)

am i doing something wrong?

thanks so much for your help!
Jamie

0
 
LVL 1

Accepted Solution

by:
stewartwb earned 500 total points
ID: 12339967
Jamie,

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, '''', '''''') +''''

output:
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
0
 

Author Comment

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

thanks for your help!!

:)
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Recently I was talking with Tim Sharp, one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. While doing some quick training with some of the Percona team, Tim brought something to my attention...
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses

886 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