Home » SQL & PL/SQL » SQL & PL/SQL » How to convert multi_byte to single_byte character (Oracle 11g)
How to convert multi_byte to single_byte character [message #684452] Thu, 10 June 2021 06:56 Go to next message
Andrey_R
Messages: 374
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 Go to previous messageGo to next message
Andrey_R
Messages: 374
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 Go to previous messageGo to next message
Michel Cadot
Messages: 67997
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 #684458 is a reply to message #684455] Thu, 10 June 2021 11:56 Go to previous messageGo to next message
Andrey_R
Messages: 374
Registered: January 2012
Location: Israel
Senior Member

Michel Cadot wrote on Thu, 10 June 2021 18:23

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]
Thank you for the explanation, much appreciated.
I do think that naming it TO_SINGLE_BYTE and making the functionality so that it is not returning it as a real single byte is pretty inadequate from Oracle's side..however..nobody's perfect Smile

Andrey
Re: How to convert multi_byte to single_byte character [message #684461 is a reply to message #684458] Thu, 10 June 2021 12:11 Go to previous messageGo to next message
Michel Cadot
Messages: 67997
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Indeed. Smile

Re: How to convert multi_byte to single_byte character [message #684480 is a reply to message #684461] Sun, 13 June 2021 09:53 Go to previous messageGo to next message
Andrey_R
Messages: 374
Registered: January 2012
Location: Israel
Senior Member

Is there a working method to convert a multi byte character(s) to true single byte character ( with specifying a character set or using some default of such ) ?
Re: How to convert multi_byte to single_byte character [message #684481 is a reply to message #684480] Sun, 13 June 2021 10:53 Go to previous messageGo to next message
Michel Cadot
Messages: 67997
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

what do you want exactly?
What currently have you exactly?

Re: How to convert multi_byte to single_byte character [message #684630 is a reply to message #684481] Thu, 15 July 2021 05:22 Go to previous message
Andrey_R
Messages: 374
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
Previous Topic: Issues with my Associative Array
Next Topic: CASE in where Clause (4 merged)
Goto Forum:
  


Current Time: Wed Oct 20 11:30:53 CDT 2021