2

How I can search for this special character

`!@#$%^&*_=[]{};<>

I have try with this code but not work that I expect

    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:],.-]' )

I have:

column_A column_B column_C column_D column_E

  1. record record ąśćół record
  2. record record re-ord re.,rd
  3. $record record record record
  4. record record record record
  5. record rec{}ordrecord record
  6. ąśćół record record record
  7. record record record reco{$rd

I need:

  1. $record record record record
  2. record rec{}ordrecord record
  3. record record record reco{$rd

but 'ąśćół' is normal polish letter and for me this is not special character also - . , and ENTER (new line) is accepted

SQL_Python_DataScience
  • 69
  • 6
  • 1
    Does this answer your question? [Search All Fields In All Tables For A Specific Value (Oracle)](https://stackoverflow.com/questions/208493/search-all-fields-in-all-tables-for-a-specific-value-oracle) – astentx Feb 22 '23 at 14:00
  • no it is not answer for my question, all column it is not so important like special character – SQL_Python_DataScience Feb 22 '23 at 14:19
  • regexp_like (column_name, '[^0-9A-Za-z- ]' ) this work but i have problem with 'enter' in string, what is 'enter' character for regexp? – SQL_Python_DataScience Feb 22 '23 at 14:21
  • 1
    Then please describe your requirement and provide sample data and desired output. *I have try with this code but is not 100% good* tells exactly nothing about the issue, because you didn't provide any criteria for good or bad and didn't specify the measurement of percentage – astentx Feb 22 '23 at 15:07

2 Answers2

1

This work that I expect

 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'
                   )
SQL_Python_DataScience
  • 69
  • 6
0

You want something like this?

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
Beefstu
  • 804
  • 6
  • 11