CODEWITHSUNDEEP
oracle11g
Anand Sunderraman
Anand Sunderraman

Reputation: 8138

How to handle special characters in query strings?

I am writing a query like

select * from fact_table where name = 'R&D'

select * from fact_table where name = 'John's'

I am using oracle 11g database. Is there any generic function or query style where in I can handle special characters like '&' and ''' in my query.

I do not want to handle each case seperately, i want a generic mechanism.

If this is a duplicate post please guide me to the original post

Upvotes: 1

Views: 6196

Answers (2)

Bharathi
Bharathi

Reputation: 1643

Use Quote Operator

select * from fact_table where name = q'#John's#'

For ampersands, it is an issue for tools like SQL*Plus

set define ~
select * from fact_table where name = 'R&D'

But in a pl/sql block, it seems working fine:

declare
    output VARCHAR2(10);
BEGIN
    SELECT name 
     INTO output
     FROM fact_table 
    WHERE name = '&1'; 

   DBMS_OUTPUT.put_line('Output: ' || output);
END;
/

SQL> @test.sql;
Enter value for 1: R&D
old   7:         WHERE name = '&1';
new   7:         WHERE name = 'R&D';
Output: R&D

PL/SQL procedure successfully completed.

Upvotes: 0

Chip Dawes
Chip Dawes

Reputation: 11

The ampersand should work fine. Some tools, like SQLPlus or SQLDeveloper use this character to indicate a bind variable. Disable this "feature" like this: set define off re-enable it like this: set define on

the single quote needs special attention. Use two single quote characters to tell the database to treat it as a literal, like this: select * from fact_table where name = 'John''s'

Upvotes: 1

Related Questions