How to convert multi_byte to single_byte character [message #684452] |
Thu, 10 June 2021 06:56 |
Andrey_R
Messages: 441 Registered: January 2012 Location: Israel
|
Senior Member |
|
|
Hi all,
I am trying to convert a multi byte character set ( asterisk "*" )
I looked up some documentation and was happy to see that we are supposed to be able to convert multi byte to single byte and vice versa:
https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions192.htm
https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions204.htm
From: MULTI BYTE , Char "0,42"
To: SINGLE BYTE, Char "42"
Unfortunately, when I use TO_SINGLE_BYTE I get what appears to be a multi_byte result:
dr
SQL> create table test as select unistr(chr(42) ) multi_exp from dual;
Table created.
SQL>
SQL>
SQL> set lines 900 pages 20000
SQL> col orig_dump_single for a20
SQL> col multi_exp for a20
SQL> col dump_multi_exp for a20
SQL> col "single_byte_?" for a20
SQL>
SQL> select dump('*') as orig_dump_single , multi_exp,dump(multi_exp) as dump_multi_exp,dump(TO_SINGLE_BYTE(multi_exp)) as "single_byte_?" from test;
ORIG_DUMP_SINGLE MULTI_EXP DUMP_MULTI_EXP single_byte_?
-------------------- -------------------- -------------------- --------------------
Typ=96 Len=1: 42 * Typ=1 Len=2: 0,42 Typ=1 Len=2: 0,42
What am I doing wrong with the conversion from multi byte to single bye ( and how to do it right ) ?
Many thanks in advance,
Andrey
[Updated on: Thu, 10 June 2021 06:59] Report message to a moderator
|
|
|
Re: How to convert multi_byte to single_byte character [message #684454 is a reply to message #684452] |
Thu, 10 June 2021 09:57 |
Andrey_R
Messages: 441 Registered: January 2012 Location: Israel
|
Senior Member |
|
|
Update:
I managed to get the desired result with ASCIISTR, however the "typ" bit for which I couldn't find meaningful documentation, is still different:
SQL> col single_byte_asciistr for a20
SQL> select dump('A') as orig_dump_single , multi_exp,dump(multi_exp) as dump_multi_exp,dump(TO_SINGLE_BYTE(multi_exp)) as "single_byte_?" ,
2 dump(asciistr(TO_SINGLE_BYTE(multi_exp))) as "single_byte_asciistr"
3 from test;
ORIG_DUMP_SINGLE MULTI_EXP DUMP_MULTI_EXP single_byte_? single_byte_asciistr
-------------------- -------------------- -------------------- -------------------- --------------------
Typ=96 Len=1: 65 A Typ=1 Len=2: 0,65 Typ=1 Len=2: 0,65 Typ=1 Len=1: 65
*EDIT* Here's the output of the same with the original asterisk value:
SQL> drop table test;
Table dropped.
SQL> create table test as select unistr(chr(42) ) multi_exp from dual;
Table created.
SQL>
SQL>
SQL>
SQL> select dump('*') as orig_dump_single , multi_exp,dump(multi_exp) as dump_multi_exp,dump(TO_SINGLE_BYTE(multi_exp)) as "single_byte_?" ,
2 dump(asciistr(TO_SINGLE_BYTE(multi_exp))) as "single_byte_asciistr"
3 from test;
ORIG_DUMP_SINGLE MULTI_EXP DUMP_MULTI_EXP single_byte_? single_byte_asciistr
-------------------- -------------------- -------------------- -------------------- --------------------
Typ=96 Len=1: 42 * Typ=1 Len=2: 0,42 Typ=1 Len=2: 0,42 Typ=1 Len=1: 42
SQL>
[Updated on: Thu, 10 June 2021 10:02] Report message to a moderator
|
|
|
Re: How to convert multi_byte to single_byte character [message #684455 is a reply to message #684452] |
Thu, 10 June 2021 10:23 |
|
Michel Cadot
Messages: 68665 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
TO_SINGLE_BYTE returns its result in the same character set than the input.
It seems yours is something like AL16UTF16, as '*' is, in fact, a single byte character (the first byte is 0), TO_SINGLE_BYTE returns the same thing.
From doc: "TO_SINGLE_BYTE returns char with all of its multibyte characters converted to their corresponding single-byte characters.".
It does not return a single byte character, it returns the single byte character (in the input character set) corresponding to the given character. It will actually be a single byte if you have a variable length character set (like AL32UTF8) but not if you have a fix length character set like AL16UTF16.
[Edit: add link]
[Updated on: Thu, 10 June 2021 10:29] Report message to a moderator
|
|
|
|
|
|
|
Re: How to convert multi_byte to single_byte character [message #684630 is a reply to message #684481] |
Thu, 15 July 2021 05:22 |
Andrey_R
Messages: 441 Registered: January 2012 Location: Israel
|
Senior Member |
|
|
Michel Cadot wrote on Sun, 13 June 2021 18:53
what do you want exactly?
What currently have you exactly?
That was a question on point, I had very little information given to me ( see this in pl/sql developer rdbms tool? I want the display from *this* to *this* ).
When I tried to clarify, I was told that issue is no longer relevant and got no further info.
Sorry for the time it took to reply, and thank you for the provided information. As always very much appreciated.
Andrey
|
|
|