OraFAQ Forum: SQL & PL/SQL » CHR(n) equivalent function for double-byte characters

Home » SQL & PL/SQL » SQL & PL/SQL » CHR(n) equivalent function for double-byte characters (Oracle 12.1.0.2.0)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

CHR(n) equivalent function for double-byte characters [message #684453]

Thu, 10 June 2021 09:23

Andrey_R
Messages: 441
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

Report message to a moderator

Re: CHR(n) equivalent function for double-byte characters [message #684456 is a reply to message #684453]

Thu, 10 June 2021 10:29

Michel Cadot
Messages: 68641
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. "

Report message to a moderator

Re: CHR(n) equivalent function for double-byte characters [message #684457 is a reply to message #684456]

Thu, 10 June 2021 11:09

Andrey_R
Messages: 441
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 ?

Report message to a moderator

Re: CHR(n) equivalent function for double-byte characters [message #684459 is a reply to message #684457]

Thu, 10 June 2021 12:05

Michel Cadot
Messages: 68641
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?

Report message to a moderator

Re: CHR(n) equivalent function for double-byte characters [message #684460 is a reply to message #684459]

Thu, 10 June 2021 12:10

Michel Cadot
Messages: 68641
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

Report message to a moderator

Re: CHR(n) equivalent function for double-byte characters [message #684462 is a reply to message #684457]

Thu, 10 June 2021 12:17

Solomon Yakobson
Messages: 3273
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.

Report message to a moderator

Previous Topic:	Compare rows
Next Topic:	Data masking in oracle

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 18 02:45:54 CDT 2024