Question Re: HTML_ENCODE() and HTML_DECODE() in Technology Q&A

HTML_ENCODE() and HTML_DECODE()

fvestjens — Fri, 12 May 2023 02:57:49 GMT

SQL Anywhere 17.0.11.6933

I want to generate an XML from the database using OPENXML(). The receiving party wants the XML to have HTML symbols for some characters.

For example they want to receive HTML_DECODE('18.00 &#176:C') to display '18.00 °C'.

How can I achieve that some characters are replaced by HTML codes as described here: https://www.rapidtables.com/web/html/html-codes.html

As in the example HTML_DECODE() is working fine but HTML_ENCODE('18.00 °C') does not result in '18.00 &#176:C' or '18.00 &deg:C'

Is there a proper way to achieve this conversion in the XML into HTML without using replace() for around 60 characters.

Re: HTML_ENCODE() and HTML_DECODE()

PCollins — Mon, 15 May 2023 07:22:36 GMT

HTML_ENCODE only supports " ("),' ('), & (&), < (&lt 😉, > (&gt 😉 and codes nn less than 0x20 (&#xnn😉

Because you are looking to encode characters greater then 0x20 then I think you will need to write a function for those specific characters they want encoded.

Re: HTML_ENCODE() and HTML_DECODE()

fvestjens — Mon, 15 May 2023 09:25:29 GMT

Do you know if something like below is possible in SQL Anywhere?

Declare @S nvarchar(max) = 'sweet shop £'
Select @S = replace(@S,MapFrom,MapTo)
 From  ( values
        ('&quot:','"'),('&amp:','&'),('&apos:',''''),('&lt:','<'),
        ('&gt:','>'),('&nbsp:',' '),('&iexcl:','¡'),
        ('&cent:','¢'),('&pound:','£'),('&curren:','¤'),('&yen:','¥'),
        ('&brvbar:','¦'),('&sect:','§'),('&uml:','¨'),
        ('&copy:','©'),('&ordf:','ª'),('&laquo:','«'),('&not:','¬'),
        ('&reg:','®'),('&macr:','¯'),('&deg:','°'),
        ('&plusmn:','±'),('&sup2:','²'),('&sup3:','³'),('&acute:','´'),
        ('&micro:','µ'),('&para:','¶'),('&middot:','·'),
        ('&cedil:','¸'),('&sup1:','¹'),('&ordm:','º'),('&raquo:','»'),
        ('&frac14:','¼'),('&frac12:','½'),('&frac34:','¾'),
        ('&iquest:','¿'),('&Agrave:','À'),('&Aacute:','Á'),
        ('&Acirc:','Â'),('&Atilde:','Ã'),('&Auml:','Ä'),('&Aring:','Å'),
        ('&AElig:','Æ'),('&Ccedil:','Ç'),('&Egrave:','È'),
        ('&Eacute:','É'),('&Ecirc:','Ê'),('&Euml:','Ë'),('&Igrave:','Ì'),
        ('&Iacute:','Í'),('&Icirc:','Î'),('&Iuml:','Ï'),('&ETH:','Ð'),
        ('&Ntilde:','Ñ'),('&Ograve:','Ò'),('&Oacute:','Ó'),
        ('&Ocirc:','Ô'),('&Otilde:','Õ'),('&Ouml:','Ö'),('&times:','×'),
        ('&Oslash:','Ø'),('&Ugrave:','Ù'),('&Uacute:','Ú'),
        ('&Ucirc:','Û'),('&Uuml:','Ü'),('&Yacute:','Ý'),('&THORN:','Þ'),
        ('&szlig:','ß'),('&agrave:','à'),('&aacute:','á'),
        ('&:','â'),('&atilde:','ã'),('&auml:','ä'),('&aring:','å'),
        ('&aelig:','æ'),('&ccedil:','ç'),('&egrave:','è'),
        ('&eacute:','é'),('&ecirc:','ê'),('&euml:','ë'),('&igrave:','ì'),
        ('&iacute:','í'),('&icirc:','î'),('&iuml:','ï'),
        ('&eth:','ð'),('&ntilde:','ñ'),('&ograve:','ò'),('&oacute:','ó'),
        ('&ocirc:','ô'),('&otilde:','õ'),('&ouml:','ö'),
        ('&divide:','÷'),('&oslash:','ø'),('&ugrave:','ù'),
        ('&uacute:','ú'),('&ucirc:','û'),('&uuml:','ü'),('&yacute:','ý'),
        ('&thorn:','þ'),('&yuml:','ÿ'),('&amp:','&'),('&deg:','°'),
        ('&infin:','∞'),('&permil:','‰'),('&sdot:','⋅'),
        ('&plusmn:','±'),('&dagger:','†'),('&mdash:','—'),('&not:','¬'),
        ('&micro:','µ'),('&euro:','€'),('&pound:','£'),
        ('&yen:','¥'),('&cent:','¢'),('&euro:','€'),('&pound:','£'),
        ('&yen:','¥'),('&cent:','¢')
       ) A (MapTo,MapFrom)
Select cast(cast(@S as xml) as nvarchar(max))

Re: HTML_ENCODE() and HTML_DECODE()

VolkerBarth — Mon, 15 May 2023 11:03:09 GMT

I don't think so, and I'm not sure whether you could use an APPLY operator with a list of mappings applied to a supplied string.

But I think a user-defined replace function is not too hard to code, unless the replace has to respect a particular order (which could also be done via sorting the entries accordningly).

A very simple starting point:

drop table if exists LT_Replace;
create local temporary table LT_Replace (strSource nvarchar(10) primary key, strTarget nvarchar(10) unique) not transactional;
-- Just the first 10 entries from the list above (just in swapped order)
insert LT_Replace values ('"', '":'),('&', '&:'),('''', '&apos:'), ('<', '<:'), ('>', '>:'), (' ', ' :'), ('¡', '¡:'), ('¢', '¢:'),('£', '£:'), ('¤', '¤:');
select * from LT_Replace order by 1;

create function fnReplace(varTest long nvarchar) returns long nvarchar
begin
   declare strReplaced long nvarchar = varTest;
   for forCrs as crs cursor for
      select strSource, strTarget from LT_Replace
   do
      set strReplaced = replace(strReplaced, strSource, strTarget);
   end for;
   return strReplaced;
end;

create variable varTest long nvarchar =
   'This is some random string with some <tags> & "quoted text", worth < £ 1,00 .';
select varTest, fnReplace(varTest);

Needless to say, I don't know why the HTML encodings in your code are shown as encodings whereas mine are not...

Re: HTML_ENCODE() and HTML_DECODE()

fvestjens — Tue, 30 May 2023 11:42:20 GMT

Thanks for the example.

I starterd working on it, but due to the fact of the current collation used in the database the string below

'°C Infineum P6072C - Do not reheat ªabove: 65 °C - ZINC PRODUCT ! ! !
'

Instead of parsing only the 'ª' in the string it parses all 'a' in the string.

I created a new database with UTF8BIN collation and with respect accents and then it is working.

However I have no idea what could be the effect of changing the collation of the database. Is there a way to do it runtime in the function?

Re: HTML_ENCODE() and HTML_DECODE()

VolkerBarth — Wed, 31 May 2023 03:55:41 GMT

Sorry, I had not thought of collation issues. Note, there's the BYTE_REPLACE() function which does work on binary characters (ignoring collations) and therefore should work as expected here.

You have to rebuild a database to change its collation, so that's quite a step. If you only need different collation behaviour now and then, you can use "collation-tailoring" for comparisons, see this FAQ with a sample using the COMPARE function in the question. That's how we do comparions when the required semantics w.r.t. to case/accents/sort order differ from our database collation...

Alternatively, if your function does return XML encoded in UTF8, you could already use NCHARs within your function to prepare your strings and use the default NCHAR collation - if that has different collation settings. (IIRC, the default NCHAR collation still ignores case and accents...)