Sociable

Friday, January 23, 2009

Convert Base64 text to ACSII text in an Inline Oracle SQL Statement

We store certain multilingual strings in Base64 in an Oracle Table. In order to select from that table, I had to convert the field to an ASCII string. Here is the syntax I used (Oracle 10g):

-- If null string, do not try to convert.
-- Trim string to 2000 characters (limit of CAST_TO_RAW)
SELECT DECODE(string_text,'','',UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_DECODE(UTL_RAW.CAST_TO_RAW(substr(string_text,1,2000))))) AS first_2000_characters,
(case when length(string_text) > 2000 then DECODE(string_text,'','',UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_DECODE(UTL_RAW.CAST_TO_RAW(substr(string_text,2001,2000))))) end) AS second_2000_characters
FROM my_table

0 comments: