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 |
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
|
|
|
|
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 ?
|
|
|
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: 68694 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 #684462 is a reply to message #684457] |
Thu, 10 June 2021 12:17 |
Solomon Yakobson
Messages: 3286 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.
|
|
|
Goto Forum:
Current Time: Sun Sep 29 00:02:08 CDT 2024
|