COMPSUPP
asked on
Oracle SQL to convert from UTF-8 to ISO-8859-1 character set
Dear Experts,
Up until recently all our comments text in a table has been encoded in ISO-8859-1 format.
e.g. this+is+a+comment
Due to a change in an application, some comments are now in the database encoded into UTF-8 format.
e.g. Flew%2Bnorth%2Bover%2BGSK%
I thought this wouldn't be a problem, as our database can cope with UTF-8 encoding and so can my new module in the application (written in Java). However, there is some legacy code that I don't understand, to do with downloading excel files, that can't cope with these UTF-8 comments.
i.e. the user downloads the excel file and sees the comment as Flew%2Bnorth%2Bover%2BGSK%
Is there a way of changing all the UTF-8 comments in the database back to ISO-8859-1 format ? I found mention of a CONVERT command, but couldn't get it to work.
thanks for your help!
Up until recently all our comments text in a table has been encoded in ISO-8859-1 format.
e.g. this+is+a+comment
Due to a change in an application, some comments are now in the database encoded into UTF-8 format.
e.g. Flew%2Bnorth%2Bover%2BGSK%
I thought this wouldn't be a problem, as our database can cope with UTF-8 encoding and so can my new module in the application (written in Java). However, there is some legacy code that I don't understand, to do with downloading excel files, that can't cope with these UTF-8 comments.
i.e. the user downloads the excel file and sees the comment as Flew%2Bnorth%2Bover%2BGSK%
Is there a way of changing all the UTF-8 comments in the database back to ISO-8859-1 format ? I found mention of a CONVERT command, but couldn't get it to work.
thanks for your help!
ASKER
Hi,
Thanks for your comment. I have now realised the problem is probably that some of the comments have been double encoded into UTF8. This has been caused by people going forwards then backwards in the web application.
Therefore, something like this:
this+is+a+comment%2B (which I assume is normal UTF8 encoding) becomes this: this%2Bis%2Ba%2Bcomment%2B
I tried looking at the SQL convert function, but it doesn't work for this.
I now think maybe I need some sort of DECODE function, to turn the double encoded comments back into the correct UTF8 format.
Please let me know if you think I am on the right track, and if there is a way to fix this double encoding problem!
thanks
Thanks for your comment. I have now realised the problem is probably that some of the comments have been double encoded into UTF8. This has been caused by people going forwards then backwards in the web application.
Therefore, something like this:
this+is+a+comment%2B (which I assume is normal UTF8 encoding) becomes this: this%2Bis%2Ba%2Bcomment%2B
I tried looking at the SQL convert function, but it doesn't work for this.
I now think maybe I need some sort of DECODE function, to turn the double encoded comments back into the correct UTF8 format.
Please let me know if you think I am on the right track, and if there is a way to fix this double encoding problem!
thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks, I think this will work if I get our SQL guru here to get it to run on a whole table.
There is a SQL function CONVERT that will do it in a select statement:
http://download.oracle.com