blob数据类型的学习记录及应用--输出放大汉字

小学徙

NX

alan7918

学习。。

philip_123456

nb

熊猫儿

oo的研究真多啊。。。。

〇〇

〇〇 发表于 2009-9-17 11:26
with tmp
as(select '汉字放大' hz from dual),
tmp2

在字符集utf8上出错了
怎么解决

〇〇

chr函数也不行了
select level,chr(to_number('b0a1','xxxx')+level-1) from dual connect by level<=9;

LEVEL CHR(
----- ----
    1 靠
    2 靠
    3 靠
    4 靠
    5 靠
    6 靠
    7 靠
    8 靠
    9 靠

〇〇

〇〇 发表于 2015-2-20 23:19
chr函数也不行了
select level,chr(to_number('b0a1','xxxx')+level-1) from dual connect by level

gbk下应该是
         I HZ
---------- --
          1 啊
          2 阿
          3 埃
          4 挨
          5 哎
          6 唉
          7 哀
          8 皑

〇〇

〇〇 发表于 2015-2-20 23:12
在字符集utf8上出错了怎么解决

融 汉字内码
GBK编码 GBK编码(10进制) 区位码(GB2312) UTF8编码
C8DA 51418 4058 %E8%9E%8D

在oraclexe上
SQL> select to_char(ascii('融'),'XXXXXXXX') from dual;

TO_CHAR(ASCII('融'
------------------
   E89E8D

SQL> select ascii(convert('融','ZHS16GBK')) from dual;

ASCII(CONVERT('融','ZHS16GBK'))
-------------------------------
                          51418

〇〇

用nchar跳过了xe的bug
SQL>  select ascii(convert(substr('融自春',3,1),'ZHS16GBK')) from dual;

ASCII(CONVERT(SUBSTR('融自春',3,1),'ZHS16GBK'))
-----------------------------------------------
                                            180
SQL>  select ascii(convert(substr(to_nchar('融自春'),3,1),'ZHS16GBK')) from dual;

ASCII(CONVERT(SUBSTR(TO_NCHAR('融自春'),3,1),'ZHS16GBK'))
---------------------------------------------------------
                                                    46266

with tmp
as(select to_nchar('春节快乐') hz from dual),
tmp2
as(select level n from tmp connect by level <=length(hz)),
tmp3
as(
select n,l,substr(dbms_lob.substr(bin_data,32,(94*(trunc(
ascii(convert(substr(hz,n,1),'ZHS16GBK' ))/256)-161)+
(mod(
ascii(convert(substr(hz,n,1),'ZHS16GBK')),256)-161))*32+1),l*4-3,4) line from

char_image,tmp,tmp2,
(select level l from dual connect by level<=16)),
tmp4
as(
select n,l,to_number(substr(line,1,1),'X')a,to_number(substr(line,2,1),'X')b,
to_number(substr(line,3,1),'X')c,to_number(substr(line,4,1),'X')e from tmp3)
select --n,l,a,b,c,e,
replace(replace(
to_char(
(floor(a/8)||mod(floor(a/4),2)||mod(floor(a/2),2)||mod(a,2))*1E12+
(floor(b/8)||mod(floor(b/4),2)||mod(floor(b/2),2)||mod(b,2))*1E8+
(floor(c/8)||mod(floor(c/4),2)||mod(floor(c/2),2)||mod(c,2))*1E4+
(floor(e/8)||mod(floor(e/4),2)||mod(floor(e/2),2)||mod(e,2)),'09999999999999999'),
'0','　'),'1','WW') hh from tmp4
order by n,l;

祝春节快乐

solomon_007

还是10年的研究，厉害！