Re: Can you explian the output of this SQL?

On Mon, 20 May 2002 16:48:18 +0100, InsaneGadgets.com <rob_at_insanegadgets.spambait.com> wrote:

>My database is i UTF8 format.
>
>If I run this script...
>
>=============
>update mytable set mycolumn = chr(229)||chr(148)||chr(180);

 That doesn't look at all right.

http://otn.oracle.com/docs/products/oracle9i/doc_library/release2/server.920/a96540/functions18a.htm#SQLRF00616 For multibyte character sets, this sort of concatenation gives different results. For example, given a multibyte character whose hexadecimal value is a1a2 (a1 representing the first byte and a2 the second byte), you must specify for n the decimal equivalent of 'a1a2', or 41378. That is, you must specify:

SELECT CHR(41378) FROM DUAL; You cannot specify the decimal equivalent of a1 concatenated with the decimal equivalent of a2, as in the following example:

SELECT CHR(161)||CHR(162) FROM DUAL; *end quote*

 Besides, 229, 148 and 180 aren't valid UTF8 single characters.

>commit;
>
>select dump(mycolumn,1010) from mytable;
>=============
>
>This should set a column to a unicode character 0x5534 and then
>display it. What it actually returns is..

 Whilst what you attempted to concatenate together is, if you take the values as decimal representations of bytes, the UTF8 representation of codepoint 0x5534, the reference from the manual above says you can't do that. Why not set it directly to chr(21812) (the decimal value of 0x5534) and allow the database to do the encoding correctly?

>=============
>Typ=1 Len=2 CharacterSet=UTF8: 148, 180
>=============
>
>If you notice the first byte #229 seems to have been stripped.
>What would cause this?

 Since what went in isn't valid UTF8 (concatention of three invalid characters), what comes out likely to be a bit of a mess anyway.

-- 
Andy Hassall (andy@andyh.org) icq(5747695) http://www.andyh.org
http://www.andyh.uklinux.net/space | disk usage analysis tool