"¿" (inverted question mark) character in oracle

sql oracle nls-lang
Mohit Tamrakar picture Mohit Tamrakar · Feb 2, 2016 · Viewed 14.5k times · Source

I found "¿" character (inverted question mark) in the database tables in place of single quote (') character.

Can any one let me know how i can avoid this character from the table.

There are many rows which contains the text with this character but not all single quotes are turning to this ¿ symbol.

I am not even able to filter the rows to update this character (¿) with single quote again.

When i user Like "%¿%" it filters me the text containing ordinary question mark (?)

Answer

Wernfried Domscheit picture Wernfried Domscheit · Feb 2, 2016

In general there are two possibilities:

  1. Your database tables really have ¿ characters caused by wrong NLS_LANG settings when data was inserted (or the database character set does not support the special character). In such case the LIKE '%¿%' condition should work. However, this also means you have corrupt data in your database and it is almost impossible to correct them because¿ stands for any wrong character.
  2. Your client (e.g. SQL*Plus) is not able to display the special character caused by wrong NLS_LANG settings or the font does not support the special character.

Which client do you use (SQL*Plus, TOAD, SQL Developer, etc.)?

What is your NLS_LANG Environment variable, resp. your Registry key HKLM\SOFTWARE\ORACLE\KEY_%ORACLE_HOME_NAME%\NLS_LANG or HKLM\SOFTWARE\Wow6432Node\ORACLE\KEY_%ORACLE_HOME_NAME%\NLS_LANG?

What do you get when you select DUMP(... , 1016) from your table?

Related questions

Get list of all tables in Oracle?

How do I query an Oracle database to display the names of all tables in it?

Read More
How do I limit the number of rows returned by an Oracle query after ordering?

Is there a way to make an Oracle query behave like it contains a MySQL limit clause? In MySQL, I can do this: select * from sometable order by name limit 20,10 to get the 21st to the 30th rows (skip the …

Read More
Oracle SQL: Update a table with data from another table

Table 1: id name desc ----------------------- 1 a abc 2 b def 3 c adf Table 2: id name desc ----------------------- 1 x 123 2 y 345 In oracle SQL, how do I run an sql update query that can update Table 1 with Table 2's name and desc using …

Read More