Anuya,

Try the function below.

Then you can use it in SQL commands, such as:

Select keyval, textval from some_table
where textval != strip_illegal_chars(textval);

This should get you close to what you want...
John.




CREATE OR REPLACE function strip_illegal_chars (instring IN varchar2
)
RETURN varchar2
as
ans NUMBER;
len_string number;
legal varchar2(100);
result varchar2(4000);
mod_string varchar2(4000);
BEGIN
-- List of allowed characters

legal := 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz';

-- check the whole string. If any characters remain after the next command,
they are invalid.
-- if no characters remain, then pass back the original string

result := LTRIM(instring, legal);
IF (result IS NULL) THEN
return(instring);
ELSE
len_string:=length(instring);
mod_string:='';
-- there are duff characters in the string. Loop through and ignore them.

for i in 1..len_string loop
result := ltrim(substr(instring,i,1), legal);
if (result is null) then
mod_string := mod_string || substr(instring,i,1);
end if;
end loop;
return(mod_string);
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN (NULL);
WHEN OTHERS THEN
RETURN (NULL);
END strip_illegal_chars;
/

-----Original Message-----
From: anuya [mailto:oracledba-ezmlmshield-x41279850.[Email address protected]

Sent: 30 March 2006 12:24
To: LazyDBA Discussion
Subject: Finding Names containing Invalid Characters

Hello,

Plz. help me in this Query.

I want to find all the names which contain an Invalid character (An
invalid character is any character other than 'A'-'Z' and 'a'-'z')



Notice: The information contained in this e-mail message and/or attachments
to it may contain confidential or privileged information. If you are not the
intended recipient, any dissemination, use, review, distribution, printing
or copying of the information contained in this e-mail message and/or
attachments to it are strictly prohibited. If you have received this
communication in error, please notify us by reply e-mail or telephone and
immediately and permanently delete the message and any attachments. Thank
you



--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html

-----------------------------------------
Information in this email may be privileged, confidential and is
intended exclusively for the addressee. The views expressed may not
be official policy, but the personal views of the originator. If
you have received it in error, please notify the sender by return
e-mail and delete it from your system. You should not reproduce,
distribute, store, retransmit, use or disclose its contents to
anyone.

Please note we reserve the right to monitor all e-mail
communication through our internal and external networks.


Oracle LazyDBA home page