CODEWITHSUNDEEP
oracle-databasespecial-characters
Sam
Sam

Reputation: 97

Special Character issue in Oracle DB

I need to update value in one table, which is having special character. Below is the Update Query I have Executed:

    UPDATE TABLE_X
        SET DISPLAY_NAME = 'AC¦', NATIVE_IDENTITY='AC¦'
        WHERE ID='idNumber'

Special Character "¦" is not getting updated in Oracle.

I have already tried below approaches:

  1. Checked the character set being used in Oracle using below query
    select * from nls_database_parameters  where parameter='NLS_CHARACTERSET';

It is having "US7ASCII" Character set.

  1. I have tried to see if any of the character set will help using below query
   SELECT CONVERT('¦ ', 'ASCII') FROM DUAL;

I have tried below different encoding:

Before Changing the character set in DB i wanted to try out 'CONVERT' function from Oracle, but above mentioned character set is either returning "Block Symbol" or "QuestionMark � " Symbol.

Any idea how can I incorporate this special symbol in DB?

Upvotes: 1

Views: 1277

Answers (1)

Justin Cave
Justin Cave

Reputation: 231651

Assuming that the character in question is not part of the US7ASCII character set, which it does not appear to be unless you want to replace it with the ASCII vertical bar character |, you can't validly store the character in a VARCHAR2 column in the database.

  • You can change the database character set to a character set that supports all the characters you want to represent
  • You can change the data type of the column to NVARCHAR2 assuming your national character set is UTF-16 which it would normally be.
  • You can store a binary representation of the character in some character set you know in a RAW column and convert back from the binary representation in your application logic.

I would prefer changing the database character set but that is potentially a significant change.

Upvotes: 1

Related Questions