9 Replies Latest reply: Jun 13, 2014 1:49 PM by Ola Yehia-Oracle RSS

    hyphen is translated to a symbol  ¿

    ricard888

      Hi all,

      For some reason a hyphen - symbol is translated to ¿ with the decode function i used below. This is  a known issue with the character translation between Source system HP  and oracle server database server

      Is there something else I can fix this.


      decode(metro_country_ind, 'C','Print – Regional', null)

      Print ¿ Regional



        • 1. Re: hyphen is translated to a symbol  ¿
          Etbin

          Maybe just decode(metro_country_ind, 'C','Print _ Regional', null) (doesn't look that different)

           

          select dump(q'{decode(metro_country_ind, 'C','Print Regional', null)}',16) x from dual

           

          X
          Typ=96 Len=57: 64,65,63,6f,64,65,28,6d,65,74,72,6f,5f,63,6f,75,6e,74,72,79,5f,69,6e,64,2c,20,27,43,27,2c,27,50,72,69,6e,74,20,e2,80,93,20,52,65,67,69,6f,6e,61,6c,27,2c,20,6e,75,6c,6c,29

           

          if not due to the forum software

           

          Regards

           

          Etbin

           

          Message was edited by: Etbin dump check added

          • 2. Re: hyphen is translated to a symbol  ¿
            ricard888

            Hi Etbin, I am not sure what i need to do. I am guessing your suggestion is replaced the hyphen with either a e2, 80, and 93.

            • 3. Re: hyphen is translated to a symbol  ¿
              odie_63

              What's your database character set?

              What's the source character set?

               

              Is the hyphen character used in a string literal (as in your decode example) or is the string coming from a column?

              • 4. Re: hyphen is translated to a symbol  ¿
                Etbin

                It's not either one or the other. Check the lengths: 55 vs. 57 as reported by length and dump functions (the bytes in red represent the hyphen)

                Consider

                 

                select length(q'{decode(metro_country_ind, 'C','Print – Regional', null)}') lx,

                       dump(q'{decode(metro_country_ind, 'C','Print – Regional', null)}',16) x

                  from dual

                 

                LXX
                55Typ=96 Len=57: 64,65,63,6f,64,65,28,6d,65,74,72,6f,5f,63,6f,75,6e,74,72,79,5f,69,6e,64,2c,20,27,43,27,2c,27,50,72,69,6e,74,20,e2,80,93,20,52,65,67,69,6f,6e,61,6c,27,2c,20,6e,75,6c,6c,29

                 

                It might be due to the forum software or APEX that I'm using or the combination of both or something else ...

                You might also use a minus sign "-" instead of a hyphen "".

                 

                Regards

                 

                Etbin

                • 5. Re: hyphen is translated to a symbol  ¿
                  michaelrozar17

                  Can you try replacing the hyphen with corresponding chr function?

                   

                  select decode('C', 'C','Print '||chr(45)||' Regional', null)

                     from dual

                  • 6. Re: hyphen is translated to a symbol  ¿
                    ricard888

                    Hi Michael, i will have wait until tomorrow to find out the result. As the SQL I am going to twick sit in a user managed database runs by the HP server. In the mean time i am finding your answer is  very Helpful.

                    • 7. Re: hyphen is translated to a symbol  ¿
                      Paul  Horth

                      ricard888 wrote:

                       

                      Hi all,

                      For some reason a hyphen - symbol is translated to ¿ with the decode function i used below. This is  a known issue with the character translation between Source system HP  and oracle server database server

                      Is there something else I can fix this.


                      decode(metro_country_ind, 'C','Print – Regional', null)

                      Print ¿ Regional



                      It's because it is not a hyphen, it's an en-dash.

                       

                      As others have said, we need to know both DBs' character set, what client software you are using to display the result and what the client character set is.

                      • 8. Re: hyphen is translated to a symbol  ¿
                        Sergiusz Wolicki-Oracle

                        As Paul mentioned, you have the en-dash (U+2013) character stored in UTF-8. If the database character set is AL32UTF8 or UTF8, the character is stored correctly. However, any software that expects ISO-8859-1 (WE8ISO8859P1) data cannot display the character. The character is present in the Windows extension to ISO-8859-1, which is the MS Code Page 1252 (WE8MSWIN1252), but not in ISO-8859-1 itself.

                         

                         

                        The solution:

                         

                         

                        i) If the client is Windows and capable of displaying WE8MSWIN1252, it should be configured properly by setting NLS_LANG=.WE8MSWIN1252

                        ii) If the client is Unix-based and not capable of displaying WE8MSWIN1252, you can use REPLACE(string,CHR(14844051),'-') to replace the character with the ASCII minus sign.

                         

                         

                         

                        Thanks,

                        Sergiusz

                        • 9. Re: hyphen is translated to a symbol  ¿
                          Ola Yehia-Oracle

                          Hi,

                           

                          To confirm it is nls_lang problem

                           

                          I suggest to start by using Oracle SQL Developer.

                          Oracle SQL  Developer is a "known good client" that needs no client side Oracle NLS  configuration. Download the latest version from  http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/  . Oracle SQL Developer does not need an Oracle client installation.

                          Once installed, preferably on a Microsoft Windows client :

                           

                               test with new data in a test table inserted trough Oracle SQL Developer

                               select the existing (application) data in Oracle SQL Developer

                           

                           

                          If  new data inserted trough Oracle SQL Developer in a test table is  displayed correctly in Oracle SQL Developer than this new data  can be  used as a "reference" to check your client's configuration.

                           

                           

                          If  existing application data is displayed correctly in Oracle SQL Developer  you are sure this data is correctly stored in the database and the  problem is pure the selecting client side ("database tools").

                           

                           

                          If  existing application data is NOT displayed correctly in Oracle SQL  Developer the inserting client config  is the first  thing to debug before trying to configure anything else seen this data  is not correctly stored in the database.

                           

                           

                          Kindly check above first and let me know what is the result,

                           

                           

                          other notes of interest :

                          Note:179133.1 The correct NLS_LANG in a Windows Environment

                          Note:264157.1 The correct NLS_LANG setting in Unix Environments

                          Note:229786.1 NLS_LANG and webservers explained.

                           

                          NLS_LANG Explained (How does Client-Server Character Conversion Work?) (Doc ID 158577.1

                           

                          5.2 I get an (inverted) question mark (? or ¿)

                           

                          Any further help is most welcome.

                           

                          Kindly mark the post as helpful/correct upon your judge to Help others to find the answer.

                           

                           

                          Kind Regards,

                          Ola Yehia