3

I'm connected to an Oracle Database (11g Release 2 - 11.2.0.4), with read-only access.

Into this database, some of the data is uploaded via, or rather as, XML - and quite a few entries contain multiple occurences of XML (special) character entities in the format of &#nnnn;.

So far I have dealt with these &#nnnn; in an Excel VBA script to convert them to Unicode characters, but I'd rather do that already in the SQL script I'm running to export the data (to Excel).

This Q&A covers pretty much the same issue, but I can't yet successfully replicate or implement the answers in my case and therefore need help.

The accepted answer in that Q&A contains SQL commands which I assume (wrongly?) I cannot use (with read-only access), like for instance create table, insert into, declare and loop.

Another answer works for me in that I can reproduce it (not in a online fiddle (how?) but in Oracle SQL Developer), albeit with two handicaps: 1) it doesn't loop and therefore would only work if the field contained only one special &#nnnn; character (one or multiple times) but not different &#nnnn; characters and 2) it fails to work with the   (non-breaking space) for a so far unidentified reason.

Building on the Q&A cited above, how can I convert these XML (special) characters to Unicode with read-only access in Oracle 11g?


Related link(s):


(Failed) Attempts thus far:

SQL 1

select regexp_replace(s, '&#([0-9]+);', u) from
(select s, unistr('\0' || REPLACE(TO_CHAR(TO_NUMBER(c), 'xxxx'), ' ', '')) u from
(select s, regexp_replace(s, '.*&#([0-9]+);.*', '\1') c from
(select 'Hallöle slovenĈina Hallöle slovenđina' s from dual)))

OUTPUT 1

Hallöle slovenđina Hallöle slovenđina

COMMENT 1

Ĉ ( = Ĉ ) is effectively "overwritten" by đ ( = đ ). That is, this script will only work for fields which contain only one and the same special character; it will overwrite all other special characters with the one character (which quite obviously is undesirable).


SQL 2

select regexp_replace(s, '&#([0-9]+);', u) from
(select s, unistr('\0' || REPLACE(TO_CHAR(TO_NUMBER(c), 'xxxx'), ' ', '')) u from
(select s, regexp_replace(s, '.*&#([0-9]+);.*', '\1') c from
(select 'Hallöle sloven ina' s from dual)))

OUTPUT 2 (error message)

ORA-30186: '\' must be followed by four hexdecimal characters or another '\' 30186. 00000 - "'\' must be followed by four hexdecimal characters or another '\'" *Cause: In the argument of SQL function UNISTR, a '\' must be followed by four hexdecimal characters or another '\' *Action: Fix the string format

COMMENT 2

For some reason, the non-breaking space ( ) seems to behave differently to other special characters here; maybe it's an Oracle exception?


SQL 3

select REGEXP_REPLACE(specialCharData,'&#([0-9]+);',unistr('\' || replace(to_char(to_number(regexp_replace(specialCharData, '.*?&#([0-9]+);.*$', '\1')), 'xxx'), ' ', '0')),1,1) as "bla", ................

OUTPUT 3 (error message)

ORA-01722: invalid number
01722. 00000 -  "invalid number"
*Cause:    The specified number was invalid.
*Action:   Specify a valid number.

COMMENT 3

specialCharData would be the name of the field/column in my database.


SQL 4

select REGEXP_REPLACE(specialCharData,'&#([0-9]+);',unistr('\' || replace(regexp_replace(specialCharData, '.*?&#([0-9]+);.*$', '\1'), ' ', '0')),1,1) as "specialChar", ................

OUTPUT 4 (error message)

ORA-30186: '\' must be followed by four hexdecimal characters or another '\' 30186. 00000 - "'\' must be followed by four hexdecimal characters or another '\'" *Cause: In the argument of SQL function UNISTR, a '\' must be followed by four hexdecimal characters or another '\' *Action: Fix the string format

COMMENT 4

specialCharData would be the name of the field/column in my database. Here I tried to prune SQL 3 by cutting away the to_char(to_number( section. Not that helpful, probably... random testing idea...

10
  • I guess you didn't waste much time studying the accepted answer you're referring to, otherwise you'd realise that create table and insert statements shown there are simply to generate sample data (which you obviously don't need). – mustaccio May 25 '20 at 20:43
  • @mustaccio I waste loads of time a lot of the time - but that's besides the point. And yes, I did try to study and understand the answer and had attempted to implement it earlier today but those attempts only gave various error messages. I shall include these (failed) attempts in an update to my question above shortly. Until then: what about the loop: Could it work in or as part of a SELECT statement? (if that's a "legitimate" newbie question...) – nutty about natty May 25 '20 at 21:42
  • @mustaccio "SQL 3" in my updated question above is a failed attempt to do something with "the beef" of the loop statement of the accepted answer... – nutty about natty May 25 '20 at 22:23
  • A PL/SQL loop cannot be a part of a select statement, but the opposite is of course possible. However, SQL is not really a good tool for string manipulation. If you have a working VBA solution, you might as well keep using it. – mustaccio May 25 '20 at 23:28
  • @mustaccio A less elegant solution I can imagine in SQL would be to compile an explicit key-value-mapping list and firing some "find-and-replace" in the SELECT statement. Would that be feasible? (TLDR: I'd prefer not having to resort to any Excel macros; rather, I'd like the SQL script to produce the output "just right" and thus being able to dump it into the Excel "as is". This is a monthly exercise and therefore I'd like to streamline my end as aerodynamically as possible, noting that I don't have the nuclear option at my disposal of scrapping the whole database and rebuilding it better.) – nutty about natty May 26 '20 at 0:31
1

The data looks like it is raw XML data without XML Tags, not the translated text. You should probably get that fixed first.

In the meantime, you can run the data through XMLTable() to do the translation for you.

with data as (
  select 'Hallöle slovenĈina Hallöle slovenđina' str from dual
)
select a.str, b.str2
from data a, xmltable( '/'
  passing xmltype( '<dat>' || a.str || '</dat>' )
  columns
   str2 varchar2(4000) path '/dat'
) b

Produces the desired result: enter image description here

8
  • What do you mean by "translated text"? The data does not contain any XML tags, only special characters, at it were, in said format (&#..;). – nutty about natty May 26 '20 at 14:49
  • 1
    "translated text" is "text without encodings such as &#264;". The phrase "unencoded text" would probably make more sense. It looks like someone produced the string (for INSERT) by simply dropping the XML tags instead of properly decoding the XML data. URL Encoding might do this. Using the wrong Character Set might be a cause also. I highly suggest you get the INSERT fixed before solving this. – Michael Kutz May 26 '20 at 15:03
  • But I can't get INSERT fixed (if I understand what's implied correctly) as that's higher up the food chain; I have read-only access and we are dealing with a dinasaur of a DBMS which won't budge; we have to work-around many bugs and shortcomings. – nutty about natty May 26 '20 at 16:25
  • 2
    DBMS becomes a dinosaur only when the vendor stops supporting that version of software. The data in them will last forever. Your superiors need to make a choice: (1) fix the actual problem once and for all. (2) replace the software. (3) continue throwing money at the problem until (1) or (2) is done. As new data is added, (3) has a chance to fail (until they pay you to fix it again). I've already posted my suggestion for solving (3). – Michael Kutz May 26 '20 at 16:45
0

One way "on foot", via a nested replace():

SELECT replace(replace(replace(col,'&#264';'Ĉ'),'&#273;','đ'),'&#160;',' ')

This might be my best shot, if a looped regex won't work under the given restrictions... No?

(Obviously the above would still need to be expanded for many more characters...)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.