| Unable to remove junk characters in Reports [message #638586] |
Mon, 15 June 2015 10:43  |
 |
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 #638695 is a reply to message #638606] |
Thu, 18 June 2015 11:48  |
|
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 #638700 is a reply to message #638699] |
Thu, 18 June 2015 12:12 |
 |
Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 #638711 is a reply to message #638704] |
Fri, 19 June 2015 00:32 |
|
Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 |
|
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 #638782 is a reply to message #638775] |
Sat, 20 June 2015 12:32 |
 |
Littlefoot
Messages: 21823
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
