Handling Special Characters


Handling Special Characters


I have a table which has a column where I can find special characters like &Altide; &Abc; etc. The total list of special characters is 110, I identified them using the logic anything between "&" and ";" is a special character and finally cross checked manually. Now I need to find and replace them in my table with space(" ").

Say the record is "ABCD123 &Altide; EFGH &ACB; &123; 123" then the record should be like "ABCD EFGH 123". To do this I have 2 problems 1st: The records which have "ABCD & this is not supposed to be a special character, putting semi colon just like that;" will loose the info between "&" and ";". This is more of business problem and I am trying to get an alternative for this by putting some logic.

2nd and the more severe problem is even if I identify the list of special characters I need to find out each and every special character or maybe put the logic of "&" and ";" and traverse each and every row of my table at each data load and replace it with space. This is highly resource consuming and must have an alternative.

Any leads to an alternative?




Re: Handling Special Characters



I have a similar question,


From my source Oracle table, Im getting a column "NAME_T" with data like 
"5%off on books % apparel "
Which I would like to convert to  "5%off on books & apparel"


How to convert Unicode hex character code to symbols??


For a given row, there are atleast 10 such characters between '&' and ';'. have to replace all 10 in "NAME_T" column.


Senior Apprentice

Re: Handling Special Characters

I don't think you can use RegExp_Replace to replace multipe patterns (but I#m not a RegEx expert).

So you probably need to use nested oReplace, one for each pattern...


Re: Handling Special Characters

Thanks for the reply Dnoeth.
Yes, I tried with OREPLACE

oreplace (oreplace (oreplace (oreplace (DESC_N,'!','!'),'#','#') ,'$','$') ,'%','%')

and there are almost 60+ such characters to get replaced for 4 columns.

When I added 60+ nested OREPLACE commands for 4 columns, got an error like [Error 3710] [SQLState HY001] Insufficient memory to parse this request, during Resolver phase. [SQL State=HY001, DB Errorcode=3710]