如何在ORACLE SQL表中查找特殊字符

czq61nw1  于 2023-03-01  发布在  Oracle
关注(0)|答案(2)|浏览(130)

如何搜索此特殊字符
`!@#$%^&*_=[]{};〈〉
我已经尝试与此代码,但没有工作,我期望

select 
        * 
    from table
    where 
        regexp_like (A, '[^0-9A-Za-z[:space:],.-]' )
        or regexp_like (B, '[^0-9A-Za-z[:space:],.-]' )
        or regexp_like (C, '[^0-9A-Za-z[:space:],.-]' )
        or regexp_like (D, '[^0-9A-Za-z[:space:],.-]' )
        or regexp_like (E, '[^0-9A-Za-z[:space:],.-]' )

我有:
列_A列_B列_C列_D列_E
1.记录
1.记录
1.$record记录记录记录
1.记录记录记录
1.记录
1.śćó
1.记录记录记录记录{$rd
我需要:
1.$record记录记录记录
1.记录
1.记录记录记录记录{$rd
但'śćó'是正常的波兰字母,对我来说,这不是特殊字符也-.,和回车(新行)是接受的

tvmytwxo

tvmytwxo1#

我期待的工作

select 
        * 
    from table
    where 
        column_A != NVL ( TRANSLATE ( column_A
                           , 'A`!@#$%^&*_=[]{};\<>'
                       , 'A'
                       )
                   , 'A'
                   )
        column_B != NVL ( TRANSLATE ( column_B 
                           , 'A`!@#$%^&*_=[]{};\<>'
                       , 'A'
                       )
                   , 'A'
                   )
        column_C != NVL ( TRANSLATE ( column_C 
                           , 'A`!@#$%^&*_=[]{};\<>'
                       , 'A'
                       )
                   , 'A'
                   )
        column_D != NVL ( TRANSLATE ( column_D 
                           , 'A`!@#$%^&*_=[]{};\<>'
                       , 'A'
                       )
                   , 'A'
                   )
        column_E != NVL ( TRANSLATE ( column_E 
                           , 'A`!@#$%^&*_=[]{};\<>'
                       , 'A'
                       )
                   , 'A'
                   )
py49o6xq

py49o6xq2#

你想要这样的东西?

create table dummy_data as 
    select rownum as id, dbms_random.string(opt, len) str
     from (
       select case round(dbms_random.value(0, 3))
                when 0 then 'a' 
                 when 1 then 'x' 
                else 'p'  
             end opt,
             round(dbms_random.value(5, 60)) len
    from dual connect by level <= 30 );

SELECT * FROM DUMMY_DATA;

ID  STR
1   UMUUJ0R5VM1T3X10TDCNIWC3MQ5ELOB041YMNEJSLT
2   _t8 }LeZhjiMB"8/a'/~a 
3   BLSE6XX6SL3M7W0DG3HH28SCHPSAT11ZH2E5DOSKEV3KW9
4   1]Mh58(l<Wa}{
5   :_QiWUkwp}V$}O
6   NC911A4SRN35CNXT2EU5H2GZ67IQQLKH
7   e"8,z$=Yvy5egvEH2KUkNoVjkitd9IMm0ZktsB i(bk4uU]c3;E
8   MgbpIsLZpWEcAghOUKOISA
9   7H02ASKO3CZRN4D5FUNPEU6YUZD
10  KbJ+QrI\l.th%>^f!Io%wshsVA%
11  PO9A47VU7AXI17XYD5VMSWW8E
12  1ILWL4V
13  FgubwibYBytNvmJHxUfG
14  ?[ngH?0!k.onN>mF(nrkO
15  86G0HP3
16  WXDBV3OBMVSDKQ59YT73G0II3U94
17  GP375CFIQPPN6216I5A7L54O
18  i\L<K,"d'ye 6s~_MB0O1 aC$q;T"EaqpZ^s\gIiYu&:%OnhVj]<a]CmOgqM
19  WxUEtr\II(97i7PQ-Z]yqd#&`@CQB0M"c0;{.by9qo#HT
20  IF5OP7KS9AXW91
21  HNcKwxXozXjTVwKeFZDLdmNOzFKKKq
22  4D8CINXIVT244RDDRZ5TSDQ4CRF4
23  3)oxevW-(~=+@cP[^g)@#|1.TL-_N9O-Zdgj"cwJC'*NR; FtK)K
24  AndzeLIEPklDuTWWEBrKrdNKdXwMGeLauJkRzKpKHEGAsxlEXliwBTHdK
25  dlEX1tGFuU5\5+{5`R
26   /W0.{B&)ax&lWEE#OSw
27  CBKOVLKDFKC3EVR
28  :V@Lc.Z"8[O-)cAWUpMjc?j\Kj?xV@%`Yp [VkEV1
29  P9P047
30  W)S<fB`F;N_brMP

with
  h (ok_chars) as (
    select '0123456789' ||          'ABCDEFGHIJKLMNOPQRSTUVWXYZ' ||
           'abcdefghijklmnopqrstuvwxyz'
    from   dual
  )
select c, count(*) as cnt
from   dummy_data cross join lateral (
         select  substr(str, level, 1) as c
         from    h
         where   instr(ok_chars, substr(str, level, 1)) = 0
         connect by level <= length(str)
       )
group  by c;

C CNT
}   4
-   5
'   3
<   4
,   2
=   2
_   5
(   5
^   3
[   4
"   7
/   3
:   3
.   6
%   5
!   2
?   4
`   4
    8
;   5
+   3
>   2
)   6
|   1
*   1
~   3
\   6
&   4
@   5
]   5
{   4
#   4
$   3

相关问题