How to replace hex characters in SQL-Script?

[former_member314792]

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,

Donn

[former_member182546 · Accepted solution]

Hi Donn Mukensnable,

Try using case statement for the hex decimal field to replace it into the new target field which you wish.

Attachment FYR.

Thanks.

With Regards,

Sudhir S

[former_member314792 · Accepted solution]

Sudhir,

Thanks for the suggestion, but the CASE statement (or, it looks like, SQL-Script in general) still does not accept a binary (hex) string as a parameter.  This is typing the SQL into HANA Studio, not using ABAP or any other application that has scripting.

However, I did find a solution:

Use BINTOHEX function to convert the original string (or part of it) to a hex-character string and then compare on that.  For example:

   where BINTOHEX(field) = '00'      will match a NUL string (length 1) while

    where field = x'00'                         does not!

This technique works in CASE and otherwise, so I'm getting the results I was hoping for.  I'm going to mark this as 'solved' for now, but I'm open to other comments as to how to make binary(hex) constants work in SQL-script.

Donn