Hypen converted to question mark when exporting data from oracle sql plus to csv.

Test - Text is getting converted to Test ? Text in csv after export.

am running this script in unix

CodePudding user response：

It is possible that the hyphen in your text is not really a hyphen, but it is an en dash or em dash. It is very common when the source data is copied from somewhere like Microsoft Word.

You can run a query like the one below to view the ASCII values of each character in the string. In my example text, I have my first "dash" as a hyphen and the second "dash" as an en dash. They look almost the same but are actually different.

WITH test_text (t) AS (SELECT 'Test -– Text' FROM DUAL)
    SELECT t,
           LEVEL                            AS char_position,
           SUBSTR (t, LEVEL, 1)             AS this_char,
           ASCII (SUBSTR (t, LEVEL, 1))     AS ascii_val
      FROM test_text
CONNECT BY LEVEL <= LENGTH (t);



              T    CHAR_POSITION    THIS_CHAR    ASCII_VAL
_______________ ________________ ____________ ____________
Test -û Text                   1 T                      84
Test -û Text                   2 e                     101
Test -û Text                   3 s                     115
Test -û Text                   4 t                     116
Test -û Text                   5                        32
Test -û Text                   6 -                      45
Test -û Text                   7 û                14844051
Test -û Text                   8                        32
Test -û Text                   9 T                      84
Test -û Text                  10 e                     101
Test -û Text                  11 x                     120
Test -û Text                  12 t                     116

CodePudding user response：

As https://stackoverflow.com/users/7110099/ej-egyed said, it's probable that you don't have a real "dash" in the data. If you know that your data is all in UTF-8, it may be that you simply need to get the data to display properly. The first thing would be to check whether your terminal is capable and set up for UTF-8, for instance by following some of the advice here: https://serverfault.com/questions/13898/how-to-find-out-if-a-terminal-supports-utf-8 If your terminal is up to it, assuming you are using sqlplus, you may need to tell it that you prefer output in UTF-8 with the NLS_LANG environment variable. For instance,

export NLS_LANG=AMERICAN_AMERICA.AL32UTF8

(the exact setting would depend on your locale). Unfortunately, it's also possible that your data is not in UTF-8 (Windows clients may be likely to be in something like "Windows Codepage 1252"). If that's the case, you'd first need to figure out what character set it is in and decide whether you want to try to convert it to UTF-8 or try to get your display to show it properly in the existing code sets.