Hi,
On our Oracle EBS system users can paste data into the system and "strange" characters are not trapped, therefore they can paste into forms from e.g. word and include non standard characters. Sorry if that sounds vague.
I need to be able to find the non alphanumeric chars in a table which are "breaking" an interfaced system which takes data from Oracle and puts some of it into an XML file. Other valid characters we don't have a problem with are e.g.
!"£$%^&*()_+-=[{]};:'@,<.>/?\|
For example, I know one character that causes a problem is the character MS Word uses to replace a dash.
e.g. if I type:
*this - that*
Word changes it to:
*This – that*
That's a character I can't type on my keyboard, and an example of a character I'd like to be able to find using SQL.
There are probably others, but all I would like to do is to find "non standard" characters.
I have one sample transaction ID I know contains the funny MS dash, so this SQL returns it:
SELECT pec.expenditure_comment
FROM pa.pa_expenditure_comments pec
WHERE REGEXP_LIKE (pec.expenditure_comment
, '(^ )|[^[:alnum:] &!"£$%^()_+=-{};:@#~,<.>/?\| AND pec.expenditure_item_id = 6445260
However, it also returns other records which don't contain funny characters, so I don't think it is working correctly.
Hence me asking for advice here. Any assistance would be much appreciated.
Thanks