Search code examples
oracleoracle-sqldeveloper

How to find special characters in a table ORACLE SQL


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


Solution

  • 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'
                       )