How to update value with special character in oracle 11g

Question

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

Answer 1

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.

Answer 2

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>