Q

Inserting special characters in varchar2

Please let me know how to insert the text "mehra & co" into the varchar2 datatype in SQL in 9i.

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.

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

Dig deeper on Using Oracle PL-SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close