I want a query which will remove special characters from this word: ِِAA07E01A. I need a result back as AA07E01A.
You can use a regular expression to do that - depending on how many chars you want to keep and how many you want to get rid of.
Here's a nice discussion on some various ways to do it
https://community.oracle.com/thread/595376 or if you want dont like regex, you could convert it to ascii str and then rip out the non-printing elements, eg
declare
x varchar2(100) := 'Hello Ä Ê ÍThere';
y varchar2(200) := asciistr(x);
res varchar2(100);
begin
while instr(y,'\') > 0 loop
res := res || substr(y,1,instr(y,'\')-1);
y := substr(y,instr(y,'\')+5);
end loop;
dbms_output.put_line(res||y);
end;
/