DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: SQL to find control Characters

  1. #1
    Join Date
    Dec 2000
    Location
    Virginia, USA
    Posts
    455
    I have a table T1 with column C1
    C1 Data type is Varchar2

    in C1 column, I have some control Characters inserted
    like & #, : % etc

    How can I write a SQL Statement to find all the rows with Control Characters, this table has over 2 Million rows.

    Thanks in Advance

  2. #2
    Join Date
    Sep 2001
    Location
    Düsseldorf, Germany.
    Posts
    588
    This query may not be efficient but hope it will give an idean for want you are looking for

    Code:
    SET DEF OFF
    
    SELECT id, name FROM test
    WHERE (name LIKE '%$%' OR
    name LIKE '%&%' OR 
    name LIKE '%*%' OR
    name LIKE '%@%' OR
    name LIKE '%!%' OR
    name LIKE '%#%' OR
    name LIKE '%^%' OR
    name LIKE '%~%' OR
    name LIKE '%`%' OR
    name LIKE '%)%' OR
    name LIKE '%-%' OR
    name LIKE '%+%' OR
    name LIKE '%:%' OR
    name LIKE '%/%' OR
    name LIKE '%(%')

    Put all wild characters in OR condition....

    HTH

    Sameer

    [Edited by Sameer on 09-09-2002 at 11:45 AM]

  3. #3
    Join Date
    Dec 2000
    Location
    Virginia, USA
    Posts
    455
    Yes, I thought of that but in some cases Invalid Characters also have Spaces , it is big mess.

  4. #4
    Join Date
    Sep 2001
    Location
    Düsseldorf, Germany.
    Posts
    588
    Originally posted by irehman
    Yes, I thought of that but in some cases Invalid Characters also have Spaces , it is big mess.
    What do you mean space...

    I did following ..wild characters also has spaces...

    Code:
    SQL> SELECT * FROM wildchar;
    
            ID NAME
    ---------- --------------------------------------------------
             1 sasas $ asa as
             1 sasas ! asa as
             1 sasas!asa as
             1 sasas&asa as
             1 sasas°asa as
             1 sasas+asa as
             1 sasas~asa as
             1 sasas*asa as
             1 sasas * asa as
             1 sasasas
             1 sasasasasasasas
             1 sameer
             1 sam $ sas ( sas
    
    13 rows selected.
    
    SQL> SELECT id, name FROM wildchar
      2  WHERE (name LIKE '%$%' OR
      3  name LIKE '%&%' OR
      4  name LIKE '%*%' OR
      5  name LIKE '%@%' OR
      6  name LIKE '%!%' OR
      7  name LIKE '%#%' OR
      8  name LIKE '%^%' OR
      9  name LIKE '%~%' OR
     10  name LIKE '%`%' OR
     11  name LIKE '%)%' OR
     12  name LIKE '%-%' OR
     13  name LIKE '%+%' OR
     14  name LIKE '%:%' OR
     15  name LIKE '%/%' OR
     16  name LIKE '%(%');
    
            ID NAME
    ---------- --------------------------------------------------
             1 sasas $ asa as
             1 sasas ! asa as
             1 sasas!asa as
             1 sasas&asa as
             1 sasas+asa as
             1 sasas~asa as
             1 sasas*asa as
             1 sasas * asa as
             1 sam $ sas ( sas
    
    9 rows selected.
    Sameer

  5. #5
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    Are you worried about every possible combination of wild cards or the ones that are most likely to cause Oracle errors when you run straight sql against the data.You could do the following.

    update t1 set c1 = replace(t1, '''', '"');
    commit;

    Just make sure you give yourself a lot of rollback

    You can also create a insert and update trigger to replace any new ' that get put into production.

  6. #6
    Join Date
    Feb 2001
    Posts
    180
    Just another solution:
    SELECT id, replace(TRANSLATE(C1,
    '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz',
    'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'),'X','') My_String
    FROM T1
    where replace(TRANSLATE(C1',
    '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz',
    'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'),'X','')
    is not null ;
    Regards
    Ben de Boer

  7. #7
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Another variation of using TRANSLATE(), but perhaps in a slightly more readable format (instead of specifying all characters that are allowed you specify characters that are not allowed):

    SELECT * FROM t1
    WHERE NVL(LENGTH(TRANSLATE(c1, '1&#,:%', '1')), 0) != NVL(LENGTH(C1), 0);
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width