Home » SQL & PL/SQL » SQL & PL/SQL » CHR(n) equivalent function for double-byte characters (Oracle 12.1.0.2.0)
CHR(n) equivalent function for double-byte characters [message #684453] Thu, 10 June 2021 09:23 Go to next message
Andrey_R
Messages: 370
Registered: January 2012
Location: Israel
Senior Member


I have a table with two columns and one row. One column of the row has a single byte value and the 2nd one is a double-byte value:

SQL> create table test as select chr(65) as Single_byte_VAL, unistr(chr(65) ) Double_byte_VAL from dual;

Table created.

SQL>
SQL> col DUMP(SINGLE_BYTE_VAL) for a22
SQL> col DUMP(DOUBLE_BYTE_VAL) for a22
SQL>
SQL> set lines 900 pages 20000
SQL>
SQL> select dump(Single_byte_VAL), dump(Double_byte_VAL) from test;

DUMP(SINGLE_BYTE_VAL)  DUMP(DOUBLE_BYTE_VAL)
---------------------- ----------------------
Typ=1 Len=1: 65        Typ=1 Len=2: 0,65

I am able to generate specific characters by their ascii encoding with the CHR(n) function:

SQL> select chr(65) from dual;

C
-
A

But when I try to do it for the double byte, it indicates that I can only use a number, no strings or complex expressions:

SQL> select chr(0,65) from dual;
select chr(0,65) from dual
       *
ERROR at line 1:
ORA-00909: invalid number of arguments


SQL> select chr('0,65') from dual;
select chr('0,65') from dual
           *
ERROR at line 1:
ORA-01722: invalid number

And the real ability that I am missing the most here is to be able to generate data per ecoding instructions, like:

SQL> insert into test values (chr(65) , chr('0,65' ) );
insert into test values (chr(65) , chr('0,65' ) )
                                       *
ERROR at line 1:
ORA-01722: invalid number
Is there CHR(n) equivalent function for multi-byte characters ?


Thanks in advance,
Andrey

Re: CHR(n) equivalent function for double-byte characters [message #684456 is a reply to message #684453] Thu, 10 June 2021 10:29 Go to previous messageGo to next message
Michel Cadot
Messages: 67923
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You have to UNISTR for that:
SQL> select unistr('\0041') from dual;
U
-
A
(x41=65)

From doc: "UNISTR takes as its argument a text literal or an expression that resolves to character data and returns it in the national character set. "

Re: CHR(n) equivalent function for double-byte characters [message #684457 is a reply to message #684456] Thu, 10 June 2021 11:09 Go to previous messageGo to next message
Andrey_R
Messages: 370
Registered: January 2012
Location: Israel
Senior Member

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

You have to UNISTR for that:
SQL> select unistr('\0041') from dual;
U
-
A
(x41=65)

From doc: "UNISTR takes as its argument a text literal or an expression that resolves to character data and returns it in the national character set. "


I have this double-byte character:

SQL> select dump('ü') from dual;

DUMP('ü')
---------------------
Typ=96 Len=2: 194,129

SQL>
How can I generate it without literally specifying it ?
Can you suggest where can I find proper mapping information on how to knowingly generate a value based on a dump info or alike ?

Re: CHR(n) equivalent function for double-byte characters [message #684459 is a reply to message #684457] Thu, 10 June 2021 12:05 Go to previous messageGo to next message
Michel Cadot
Messages: 67923
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

SQL> with data as (select 'Typ=96 Len=2: 194,129' dp from dual)
  2  select 'unistr(''\'||
  3         to_char(
  4           to_number(substr(dp,instr(dp,':')+2,instr(dp,',')-instr(dp,':')-2))*256
  5           +to_number(substr(dp,instr(dp,',')+1)),
  6           'fm000X')||
  7         ''')' res
  8  from data
  9  /
RES
----------------
unistr('\C281')
But it may be easier with the result of ASCII function, what does it return?

Re: CHR(n) equivalent function for double-byte characters [message #684460 is a reply to message #684459] Thu, 10 June 2021 12:10 Go to previous messageGo to next message
Michel Cadot
Messages: 67923
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

It is easier with ASCIISTR:
SQL> with data as (select to_nchar(unistr('\C281')) val from dual)
  2  select val, asciistr(val) res
  3  from data
  4  /
V RES
- ----------
■ \C281
Smile
Re: CHR(n) equivalent function for double-byte characters [message #684462 is a reply to message #684457] Thu, 10 June 2021 12:17 Go to previous message
Solomon Yakobson
Messages: 3067
Registered: January 2010
Location: Connecticut, USA
Senior Member
SQL> select dump('ü',16) from dual;

DUMP('ü',16)
-------------------
Typ=96 Len=2: c2,81

SQL> select unistr('\c281') from dual;

U
-
┐

SQL> select utl_raw.cast_to_varchar2('c281') from dual;

UTL_RAW.CAST_TO_VARCHAR2('C281')
------------------------------------------------------------------------------------------------------------------------------------
ü

SQL>
But keep in mind, it isn't about how many bytes it is about character set. Code C281 is character ü code in character set AL32UTF8. So running select utl_raw.cast_to_varchar2('c281') from dual can return different character when run on databases with different character sets because character ü code is character set dependent. For example, look what happens if database character set is TR8MSWIN1254:

COLUMN SOURCE_CHARSET FORMAT A14
COLUMN TARGET_CHARSET FORMAT A14
COLUMN SOURCE_CHARACTER FORMAT A16
COLUMN TARGET_CHARACTER FORMAT A16
COLUMN CORRECT_DUMP FORMAT A24
COLUMN CORRECT_RAW FORMAT A11
COLUMN CORRECT_TARGET_CHARACTER FORMAT A24
SELECT  'AL32UTF8' SOURCE_CHARSET,
        'TR8MSWIN1254' TARGET_CHARSET,
        'ü' SOURCE_CHARACTER,
        UTL_RAW.CAST_TO_VARCHAR2(UTL_RAW.CONVERT('c281','TR8MSWIN1254','AL32UTF8')) TARGET_CHARACTER,
        DUMP(CONVERT('ü','TR8MSWIN1254'),16) CORRECT_DUMP,
        REPLACE(SUBSTR(DUMP(CONVERT('ü','TR8MSWIN1254'),16),14),',') CORRECT_RAW,
        UTL_RAW.CAST_TO_VARCHAR2(
                                 UTL_RAW.CONVERT(
                                                 REPLACE(SUBSTR(DUMP(CONVERT('ü','TR8MSWIN1254'),16),14),','),
                                                 'AL32UTF8',
                                                 'TR8MSWIN1254'
                                                )
                                ) CORRECT_TARGET_CHARACTER
  FROM  DUAL
/

SOURCE_CHARSET TARGET_CHARSET SOURCE_CHARACTER TARGET_CHARACTER CORRECT_DUMP             CORRECT_RAW CORRECT_TARGET_CHARACTER
-------------- -------------- ---------------- ---------------- ------------------------ ----------- ------------------------
AL32UTF8       TR8MSWIN1254   ü                ┐                Typ=1 Len=1: 81          81          ü

SQL>
As you can see, character ü code in character set TR8MSWIN1254 is 81 and not c281.

SY.
Previous Topic: Compare rows
Next Topic: Data Extraction
Goto Forum:
  


Current Time: Mon Aug 02 16:24:50 CDT 2021