CODEWITHSUNDEEP
oracle-databaseoracle11gora-00911
Deepanshu biyani
Deepanshu biyani

Reputation: 101

How to update value with special character in oracle 11g

I want to update the password having special characters ''?@ @C $4 ABC (starting two characters are two single quotes) in Xyz table.

I am trying the following query 

UPDATE Xyz set password="''?@ @C $4" where user_no like '%123%';

But I am getting error as 

ORA-00911: invalid charachter

Upvotes: 0

Views: 778

Answers (2)

Littlefoot
Littlefoot

Reputation: 142705

The q-quoting mechanism helps in such situations, when you have to work with multiple single quotes within the string.

SQL> desc xyz
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 USER_NO                                            NUMBER
 PASSWORD                                           VARCHAR2(20)

SQL> select * From xyz;

   USER_NO PASSWORD
---------- --------------------
       123 a

SQL> update xyz set password = q'[''?@ @C $3]' where user_no = 123;

1 row updated.

SQL> select * From xyz;

   USER_NO PASSWORD
---------- --------------------
       123 ''?@ @C $3

SQL>

Upvotes: 1

Abhishek Prusty
Abhishek Prusty

Reputation: 864

Are you pasting the query from a different editor or IDE ? or Maybe copying from windows applications to Linux? In that case, there may be non-printable characters present. If so, you could retype (not copy-paste) the SQL statement and try.

Also, double quotes aren't commonly used in SQL. You may want to replace them with single quotes.

Upvotes: 0

Related Questions