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: 369
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: 369
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: 67885
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: 369
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: 67885
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: 369
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 message
Michel Cadot
Messages: 67885
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

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

Previous Topic: Data masking in oracle
Next Topic: dbms_scheduler.create_job() throws error ORA-27362
Goto Forum:
  


Current Time: Mon Jun 21 07:36:22 CDT 2021