SQL to find control Characters

[irehman]

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

[Sameer]

This query may not be efficient but hope it will give an idean for want you are looking for

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]

[irehman]

Yes, I thought of that but in some cases Invalid Characters also have Spaces , it is big mess.

[Sameer]

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...

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

[gandolf989]

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.

[bensr]

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 ;

[jmodic]

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);