-
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
-
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]
-
Yes, I thought of that but in some cases Invalid Characters also have Spaces , it is big mess.
-
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
-
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.
-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|