Ask the Expert

Inserting special characters in varchar2

Please let me know how to insert the text "mehra & co" into the varchar2 datatype in SQL in 9i. Can it be done without allowing Oracle to allow the user to enter the text at run time? Is there any method to insert along with the query itself? Can we insert the special characters in a table? If so please let me know with an example.

    Requires Free Membership to View

In order for values to be entered into a field without having the user enter the value, the table must be created with a default value. If you are using SQL*Plus and part of the default value is a special character, you must set the DEFINE parameter to off. Below, I connected to scott, then set define to off. Next I dropped my table then re-created it with the default value with the special character "&".
SQL> connect scott/tiger
Connected.

SQL> set define off

SQL> drop table special_chars;

Table dropped.

SQL> create table special_chars(id number, 
spec_char varchar2(100) default 'mehra & co');

Table created.
In the next set of statements, I inserted a value only in the ID field. Notice when I perform a select, the value for the field spec_char shows "mehra & co".
SQL> insert into special_chars(id)  values (1);

1 row created.

SQL> select id, substr(spec_char,1,20)  
from special_chars;

        ID SUBSTR(SPEC_CHAR,1,2                                                 
---------- --------------------                                                 
         1 mehra & co                                                           
Next, I performed an insert using a subquery. Then when I performed a query, the query showed two records.
SQL> insert into special_chars(id, spec_char)
  2  select id, spec_char
  3  from special_chars
  4  where id = 1;

1 row created.

SQL> select id, substr(spec_char,1,20)  
from special_chars;

        ID SUBSTR(SPEC_CHAR,1,2
---------- --------------------                                                 
         1 mehra & co                                                           
         1 mehra & co          

This was first published in July 2006

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: