Remove the special

[ramakrishnamanne]

Hi All,

I have a description field with special characters and unwanted number, I want to remove that special characters and unwanted numbers which is BOLD in the example description in report studio report.

Description Field:-
31- Customer Reference Number: 2388094412 ~32- BUYER NAME OR DEPARTMENT:F13 -~33- DELIVERY REQUESTED ON 06/01/11 ~34- CANCEL AFTER 06/16/15 ~35-ATTENTION ELAINE HOWIE~36- CANCEL ALL BACKORDERS. CHECK COSTS****|| BEFORE SHIPPING~37-FAX 43575 8795~10-CALL FOR APPT: 450/642-0101.  ~30-GST/HST #10574 8278 RT0001##~

Note:- We don't know how many special characters , unwanted numbers and specific length of characters.
Cognos :-10.2.2 version
Database :- Oracle
Relational database

Thanks
RK

[CognosPaul]

Stuff like this should be cleaned in the ETL. Anything you do will always be slower and dirtier than the what is possible in the ETL.

Oracle has a function called REGEX_REPLACE that will allow you to replace strings with a regex search. Since I don't have Oracle handy at the moment, I can't test it. But the code will probably be something like:

regexp_replace([Field], '^[0-9]+-|~[0-9]+-', '')

That should find the numbers, but take care, it's very easy to get unwanted results with regex.

[ramakrishnamanne]

 Thanks CognosPaul,
I will try with that function and let you know how it will work.

Thanks
RK

[ramakrishnamanne]

Hi Paul,

I am not able to find the function which is you suggested in database functions in cognos.

In crystal report they have written code for removing special characters and unwanted numbers like this

local stringvar notes:= trim(Replace (left(mid({Description},4),len(mid({Description},4))-1),"#" ,"
" ))+"
";
while instr(notes,'~')>0
do (
local stringvar snipe:= mid(notes,instr(notes,'~'),4);
notes:= replace(notes,snipe,"");
);
notes

They have written loop.

Using this code shall we create it in cognos report side? Can you please help anyone on same to write in cognos report side?

Thanks
RK

[CognosPaul]

Crystal allows for some programming logic on the resultset after it's been returned. In this case it looks like a VBA variant of some sort. Cognos doesn't give us this flexibility, but there are plenty of workarounds.

The function isn't listed in Cognos as it's not officially supported. Try using it anyway. You may need to wrap the function in curly braces and use the database field name to get it to work. An alternative is to create a UDF and import that into framework.

[ramakrishnamanne]

Thanks Paul.

What you suggested oracle function is worked for me


thanks
RK