首页 » ORACLE » sql查出值中有特殊字符(不显示字符)

sql查出值中有特殊字符(不显示字符)

SQL> create table testchar(id int ,name varchar2(20));

Table created.

SQL> insert into testchar values(1,’anbob.com’);

1 row created.

SQL> insert into testchar values(2,’anbob.com’||chr(10));

1 row created.

SQL> select * from testchar;

ID NAME
———- —————————————-
1 anbob.com
2 anbob.com

SQL> set linesize 200
SQL> run
1* select name,dump(name) du,lengthb(name) from testchar

NAME                 DU                                                 LENGTHB(NAME)
——————– ————————————————– ————-
anbob.com            Typ=1 Len=9: 97,110,98,111,98,46,99,111,109                    9
anbob.com            Typ=1 Len=10: 97,110,98,111,98,46,99,111,109,10               10

SQL> select * from (
2  select length(regexp_replace(name,'[[:cntrl:]]’)) newlen,length(name) leng,name  from testchar
3  ) where newlen<>leng;

NEWLEN       LENG NAME
———- ———- —————————————-
9         10 anbob.com

打赏

对不起,这篇文章暂时关闭评论。