Issues while storing a long dash in oracle

Hi,

I work on an Oracle9 database which is loaded through sqlldr. The issue that I am facing is because of a special character known as long dash "-" (the normal one is "-") which appears in the flat file used to load the database. Whenver such character appears in the file the database processes it and it appears as "¿" in the database. So the data coming as "Finance - Fulfillment Factory" in the flat file is stored as "Finance ¿ Fulfillment Factory" in the database. The main reason for me posting this issue is because when I format this data according to the needs of my application it gives me the wrong result. I use a command like below:

nvl(rpad(UPPER(substr(nvl( substr(column1,1,instr(column1,'(')-1),column1),

1,24)),24),'                        ')

This command is supposed to return me 24 characters in all situations but it fails when the data contains that character "¿". It returns only 23 characters in this situation. I guess that is because that character takes 2 bytes instead of normal 1 and the rpad pads the data with 24 bytes and not 24 characters. So since this character takes 2 bytes it returns only 23 characters.
I have also checked and the database uses the UTF8 character set.

Not sure how to solve this issue...any help would be much appreciated.

Thanks! Received on Tue Mar 11 2008 - 01:53:23 CDT