Skip to Content
Jun 19, 2015 at 11:46 PM

How to replace hex characters in SQL-Script?


I've got a table that contains rows having the 'NUL' (x'00') in a varchar string. It's not just that character by itself, but mixed in with other printable ones and the occasional CR or LF. I'm looking for a way to replace these with blanks. I've tried what seems like it should work in SQLscript:

REPLACE(field, x'00', ' ')

...and this does not generate an error, but it doesn't do the replacement either. Checking further it seems that a hex or binary constant isn't being interpreted, so it's not just the sometimes-problematic NUL.

Currently at SPS06, going to SPS09; I can look up my HANA Studio version if that matters...

Thanks In Advance,