Home » Developer & Programmer » Reports & Discoverer » Unable to remove junk characters in Reports
Unable to remove junk characters in Reports [message #638586] Mon, 15 June 2015 10:43 Go to next message
venki8286
Messages: 29
Registered: May 2015
Location: Hyderabad
Junior Member
Hi All,

I am trying to remove junk characters in PO NUMBER field in INVOICE REPORT using REGEXP_REPLACE function.
But, PO Number which are having prefix with more Junk characters, REGEXP_REPLACE function is unable to remove those junk characters.

Can any body help me.

Venkatesh
Re: Unable to remove junk characters in Reports [message #638594 is a reply to message #638586] Mon, 15 June 2015 11:54 Go to previous messageGo to next message
Michel Cadot
Messages: 67235
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

To remove junk characters TRANSLATE is the function.

Re: Unable to remove junk characters in Reports [message #638606 is a reply to message #638594] Mon, 15 June 2015 14:07 Go to previous messageGo to next message
Littlefoot
Messages: 21574
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
It would be easier to suggest something if you provided sample valid & invalid data.
Re: Unable to remove junk characters in Reports [message #638695 is a reply to message #638606] Thu, 18 June 2015 11:48 Go to previous messageGo to next message
venki8286
Messages: 29
Registered: May 2015
Location: Hyderabad
Junior Member
Hi Littlefoot,

If I am using REGEXP_REPLACE function in Toad it is coming correct output by removing junk characters, But when I am using REGEXP_REPLACE function in Invoice Report and It is showing me the error when more junk characters are having in PREFIX, when junk characters are having in Postfix then it coming correct.

See the example below.

Toad:
=====
Example: select regexp_replace('2087624','(:cntrl:)',NULL) from dual
Output: 2087624

Report:
=======
rpad(regexp_replace(purchase_order,'(:cntrl:)',NULL),50,'-')

Output in XML (Showing Error):
================
XML Parsing Error: not well-formed
Line Number 60, Column 38:
<PURCHASE_ORDER_NUMBER>2087624</PURCHASE_ORDER_NUMBER>

Unable to remove junk characters in Prefix using regexp_replace function

[Updated on: Thu, 18 June 2015 11:49]

Report message to a moderator

Re: Unable to remove junk characters in Reports [message #638696 is a reply to message #638695] Thu, 18 June 2015 11:51 Go to previous messageGo to next message
Michel Cadot
Messages: 67235
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

So you don't want I answer and help you.

Re: Unable to remove junk characters in Reports [message #638697 is a reply to message #638696] Thu, 18 June 2015 11:55 Go to previous messageGo to next message
venki8286
Messages: 29
Registered: May 2015
Location: Hyderabad
Junior Member
Hey Michel,

Don't think like that.
I want your suggestion also. i have used your function also. But it is not coming.
Actually he asked example so thats why i give reply to him.
Please help me.

Venkat
Re: Unable to remove junk characters in Reports [message #638698 is a reply to message #638697] Thu, 18 June 2015 12:00 Go to previous messageGo to next message
Michel Cadot
Messages: 67235
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

And you didn't provide what was asked: example of valid and invalid data.

Quote:
i have used your function also. But it is not coming.


Which help in no way to know why "it is not coming" and what "is coming" should be.

Re: Unable to remove junk characters in Reports [message #638699 is a reply to message #638698] Thu, 18 June 2015 12:07 Go to previous messageGo to next message
venki8286
Messages: 29
Registered: May 2015
Location: Hyderabad
Junior Member
If i am using Transalate function

select Translate('2087624','(:cntrl:)','') from dual

Output is showing Null
Re: Unable to remove junk characters in Reports [message #638700 is a reply to message #638699] Thu, 18 June 2015 12:12 Go to previous messageGo to next message
Michel Cadot
Messages: 67235
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ becauue the last parameter of TRANSLATE is NULL. As specified in the documentation the result is then NULL.
2/ TRANSLATE does not know regexp so you have to list all characters you don't want.
For your example:
SQL> select Translate('^O^P^Q^R^T^O^P^Q^R^T^O^P^Q^R^T2087624','1^O^P^Q^R^T','1') from dual;
TRANSLA
-------
2087624

(^O is Ctl-O character not ^ followed by O...)

[Updated on: Thu, 18 June 2015 12:17]

Report message to a moderator

Re: Unable to remove junk characters in Reports [message #638704 is a reply to message #638700] Thu, 18 June 2015 15:35 Go to previous messageGo to next message
venki8286
Messages: 29
Registered: May 2015
Location: Hyderabad
Junior Member
I have used like below in report

rpad(Trim(Translate('a','1^','1')),50,'-')

but it showing again error in report

Error:

XML Parsing Error: not well-formed
Line Number 60, Column 38: <PURCHASE_ORDER_NUMBER>2087624</PURCHASE_ORDER_NUMBER>
-------------------------------------^

[Updated on: Thu, 18 June 2015 15:36]

Report message to a moderator

Re: Unable to remove junk characters in Reports [message #638711 is a reply to message #638704] Fri, 19 June 2015 00:32 Go to previous messageGo to next message
Michel Cadot
Messages: 67235
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Because you didn't put the correct value(s) in the second parameter of TRANSLATE or report wrongly interprets them.
I showed you it works.
Use CHR function to put control characters it is safer:
SQL> select Translate('^O^P^Q^R^T^O^P^Q^R^T^O^P^Q^R^T2087624',
  2                   '1'||chr(15)||chr(16)||chr(17)||chr(18)||chr(19)||chr(20),
  3                   '1') res
  4  from dual;
RES
-------
2087624

[Updated on: Fri, 19 June 2015 00:33]

Report message to a moderator

Re: Unable to remove junk characters in Reports [message #638775 is a reply to message #638711] Sat, 20 June 2015 06:33 Go to previous messageGo to next message
venki8286
Messages: 29
Registered: May 2015
Location: Hyderabad
Junior Member
Hi Michel

I have tried by using CHR fuction. In Toad data is showing correctly. But in reports it is show error

Error:

XML Parsing Error: not well-formed
Line Number 60, Column 38: <PURCHASE_ORDER_NUMBER>2087624</PURCHASE_ORDER_NUMBER>

Here you can see junk character not removing..Copy line and paste in notepad so that you can able see junk characters

Venkatesh

[Updated on: Sat, 20 June 2015 06:35]

Report message to a moderator

Re: Unable to remove junk characters in Reports [message #638776 is a reply to message #638775] Sat, 20 June 2015 07:41 Go to previous messageGo to next message
Michel Cadot
Messages: 67235
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Sorry I can't help more as I have not the tools, tables, data and so on you have.

Re: Unable to remove junk characters in Reports [message #638782 is a reply to message #638775] Sat, 20 June 2015 12:32 Go to previous message
Littlefoot
Messages: 21574
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
venki8286 wrote on Sat, 20 June 2015 13:33

I have tried by using CHR fuction. In Toad data is showing correctly. But in reports it is show error


Create a (stored) function, as it works correctly. Call it from report's query, passing PO NUMBER value as a parameter and the function will return the correct value.
Previous Topic: Need help in Modifying UIFONT.ALI
Next Topic: dbms_session
Goto Forum:
  


Current Time: Wed Jul 08 17:43:51 CDT 2020