一次生僻字的问题处理

一次生僻字的问题处理




客户有个名字 生僻字 存不到数据库里面，表结构又不想调整，我们想了个改应用的办法来处理相关问题

测试环境 情况:
数据库版本:
[oracle@zc ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Dec 2 14:08:29 2025
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle. All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> show parameter name

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cdb_cluster_name string
cell_offloadgroup_name string
db_file_name_convert string
db_name string tt
db_unique_name string tt
global_names boolean FALSE
instance_name string tt
lock_name_space string
log_file_name_convert string
pdb_file_name_convert string
processor_group_name string

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string service_names,tt1

字符集情况
SQL> select * from v$nls_parameters;

PARAMETER VALUE CON_ID
---------------------------------------------------------------- ---------------------------------------------------------------- ----------
NLS_LANGUAGE AMERICAN 0
NLS_TERRITORY AMERICA 0
NLS_CURRENCY $ 0
NLS_ISO_CURRENCY AMERICA 0
NLS_NUMERIC_CHARACTERS ., 0
NLS_CALENDAR GREGORIAN 0
NLS_DATE_FORMAT DD-MON-RR 0
NLS_DATE_LANGUAGE AMERICAN 0
NLS_CHARACTERSET AL32UTF8 0
NLS_SORT BINARY 0
NLS_TIME_FORMAT HH.MI.SSXFF AM 0

PARAMETER VALUE CON_ID
---------------------------------------------------------------- ---------------------------------------------------------------- ----------
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM 0
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR 0
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR 0
NLS_DUAL_CURRENCY $ 0
NLS_NCHAR_CHARACTERSET AL16UTF16 0
NLS_COMP BINARY 0
NLS_LENGTH_SEMANTICS BYTE 0
NLS_NCHAR_CONV_EXCP FALSE 0

19 rows selected.

首先 𮧵 转成 ASCII码

转换后，我们测试下转换的 ASCII码 能不能正常查询
select utl_raw.cast_to_nvarchar2('d87addf5') from dual
查询结果，可能看到能正常查询
UTL_RAW.CAST_TO_NVARCHAR2('D87ADDF5')
1 𮧵

我们创建测试表:
create table char_test (id int,name_nchar nvarchar2(20));

插入数据
insert into char_test values (1,(select utl_raw.cast_to_nvarchar2('d87addf5') from dual));

查询数据：
select * from char_test
ID NAME_NCHAR
1 𮧵
可以看到能够正常查询，然后我们在修改之前的查询语句

创建一个测试表 当做原表

create table char_test2 (id int,name_char varchar2(20));
插入数据
insert into char_test2 values (1,'王玮');
查询模拟的正式表
select * from char_test2;
ID NAME_CHAR
1 王玮

我们想要查询的时候显示完整的带生僻字的名字可以通过下面的办法改写sql

select CASE a.id
WHEN 1 THEN TO_NCHAR('王玮') || (select name_nchar from char_test where id=1)
ELSE TO_NCHAR(a.name_char) -- 将VARCHAR转换为NCHAR
END as xingming from char_test2 a;

查询结果：
XINGMING
1 王玮𮧵

这样我们就查询到了想要的结果