Skip to Main Content
  • Questions
  • Want to remove special characters from this ِِAA07E01A. Need to get result as AA07E01A

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Srinivasa.

Asked: May 01, 2017 - 6:54 am UTC

Last updated: May 03, 2017 - 1:01 am UTC

Version: 11.1

Viewed 1000+ times

You Asked

I want a query which will remove special characters from this word: ِِAA07E01A. I need a result back as AA07E01A.

and Connor said...

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


Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

replace characters with accent with their base letter

Marcus, May 02, 2017 - 10:57 am UTC

I think it is not about removing the characters. The example suggests that he wants them replaced by the base character.
I once had a similar problem
https://community.oracle.com/thread/1117030
Connor McDonald
May 03, 2017 - 1:01 am UTC

Thanks for your input.

More to Explore

DBMS_OUTPUT

More on PL/SQL routine DBMS_OUTPUT here