How to Enter Special Characters Like "&" in Oracle Database

How to enter special characters like & in oracle database?

If you are in SQL*Plus or SQL Developer, you want to run

SQL> set define off;

before executing the SQL statement. That turns off the checking for substitution variables.

SET directives like this are instructions for the client tool (SQL*Plus or SQL Developer). They have session scope, so you would have to issue the directive every time you connect (you can put the directive in your client machine's glogin.sql if you want to change the default to have DEFINE set to OFF). There is no risk that you would impact any other user or session in the database.

How to handle/use special characters like percent (%) and ampersand (&) in Oracle SQL queries

If you want to match Field_Name values that contain 'bla%bla&2', then you need to write this:

set define off
Select * From Some_Table Where Field_Name Like '%bla\%bla&2%' escape '\';

You get to specify which character you want to use to escape a following character (thanks should go to mathguy, not me). You also have to set define off to prevent sqlplus from trying to substitute values in a string.

If, however, you want to match Field_Name values that exactly equal the given string, then you do this instead:

set define off
Select * From Some_Table Where Field_Name = 'bla%bla&2';

Oracle SQL escape character (for a '&')

the & is the default value for DEFINE, which allows you to use substitution variables. I like to turn it off using

SET DEFINE OFF

then you won't have to worry about escaping or CHR(38).

How to insert Special Characters from PHP in Oracle?

The only character you need to escape is ' (because you're using single quotes as string delimiters). So that's:

$query1 ="INSERT INTO sample(po_number , created_at , customer_firstname , customer_lastname , customer_email , 
shipping_description , ship_to_firstname, ship_to_lastname, ship_to_company, ship_to_street, ship_to_city, ship_to_country_id, ship_to_postcode, ship_to_telephone, increment_id) VALUES(".$result_str_order.")"; 

where $result_str_order = '100','21-Mar-2011','Sam','Right','sam.right@sasmple.com','Flight','Samy',
'RTR','SR INC','222,M.G.Bank\'s-Pipeline/Rd','Newyork','US','411230','999856230','20000507'

---

Related Topics