Replace Oracle hex values

I'm having an issue with hex values being stored in an oracle database.   The fields containing the hex values appear to be null whenever I search for null values in the database.   The oracle data is being written to an XML file.  However, when I try to validate the XML, I receive the following error:

', hexadecimal value 0x0C, is an invalid character

Is there anyway to identify and replace hex characters in oracle?  I would like to fix this in my stored procedure before the data is accessed by the .net application.  

Thanks
phoen08Asked:
Who is Participating?
 
slightwv (䄆 Netminder) Commented:
Apppear to be null and are null are different.  Null is 'nothing'.  My guess is it appears to be white space.

When selecting it out of the database try:

Regexp_replace(column,'[[:space:]]',' ')

I'm not sure what the hex value 0C is but if it is considered 'space' it should work.

If not, you might need to add to the replace call.
0
 
gatorvipCommented:
0x0C is form feed (\f)

http://en.wikipedia.org/wiki/ASCII#ASCII_control_characters

As slightwv says, "space" includes a form feed character
http://psoug.org/reference/regexp.html
0
 
phoen08Author Commented:
The replace function slightwv provided works great but I'm afraid I may need to trap all invalid hex characters.  I'm thinking about writing a method in .NET to remove all invalid characters from the XML file before they are validated.  

I'm not sure why the column containing the hex OC value returns null when I peform a select query.  When the column value is written to the XML file it contains the hex values and also valid text
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
slightwv (䄆 Netminder) Commented:
>>sure why the column containing the hex OC value returns null when I peform a select query.

What makes you think it is retuning 'null'?   Displaying a non-printable character on the screen might 'appear' to be null but the database has a value for that column.

>>but I'm afraid I may need to trap all invalid hex characters.

You can tweak the function to replace everything EXCEPT a list of  'valid' chacters but you need to define what is 'valid'.

There are a few other character classes similar to SPACE that might work for you.
0
 
phoen08Author Commented:
>>What makes you think it is retuning 'null'?   Displaying a non-printable character on the screen might 'appear' to be null but the database has a value for that column.

I tested the value for null on the oracle side.  I know the value is returning null because I used the NVL fuction to replace null with 0.  Also used a select query to find the column that "is null".
0
 
slightwv (䄆 Netminder) Commented:
If it is stored as a true 'null' in oracle, then something else is adding the HEX value.

It is impossible for "nvl(column,0)" to return a 0 if the column contains a '0C'.  Either impossible or a pretty huge bug in your version of Oracle.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.