0

I do find a problem with enconding of characters into Oracle

They are two inverted interrogation characters coming from imported data.

How can i search for two inverted characters into Oracle so i can see how many lines have this problem ?

Ângelo Rigo
  • 2,039
  • 9
  • 38
  • 69
  • The inverted characters are characters that your database or client does not know how to display. Look in the source data and check your NLS settings for the database – kevinskio Mar 07 '16 at 18:07
  • 1
    And the NLS settings for the session you're querying from when you see those, including your NLS_LANG environment variable. – Alex Poole Mar 07 '16 at 18:21
  • 1
    You can use the dump function to show the byte values of a string. This way you can figure out what is actually stored in the table. select dump(column) from table; – Rene Mar 08 '16 at 08:08
  • 1
    `¿` is a regular (and fairly common) character in Spanish. Shall we assume you don't handle Spanish texts? – Álvaro González Mar 08 '16 at 08:10
  • Yes i am on Brasil and this database have imported data from Mysql and does not have the same encoding – Ângelo Rigo Apr 01 '16 at 11:12

1 Answers1

1

I assume by "inverted interrogation character" you mean character ¿.

There are two possibilities:

  1. Character ¿ is actually stored in your database, because your database character set (check with SELECT * FROM V$NLS_PARAMETERS WHERE PARAMETER LIKE '%CHARACTERSET') is not capable to support special characters you tried to import.

    You can find affected rows with SELECT * FROM TABLE_NAME WHERE REGEXP_LIKE(COL_NAME, '¿');

  2. Your client (e.g. SQL*Plus) is not able to display the special character and substitute those by placeholder ¿. In this case set your NLS_LANG value properly, see this answer for more details.

Unfortunately you did not tell us how you imported your data nor any of your characters sets. Thus I cannot provide you a guideline to set NLS_LANG properly.

Community
  • 1
  • 1
Wernfried Domscheit
  • 54,457
  • 9
  • 76
  • 110
  • It is a mysql import i did not have much details but using SELECT * FROM TABLE_NAME WHERE REGEXP_LIKE(COL_NAME, '¿'); i can track how many records are afeccted and maybe to fix – Ângelo Rigo Mar 08 '16 at 11:13
  • Can i use REGEXP_LIKE to find accents ? something like NOT (A-Z a-z) SELECT * FROM clients WHERE REGEXP_LIKE(name, '[A-Z] '); – Ângelo Rigo Mar 30 '16 at 12:15