Page 1 of 1

Replacing "SUB" character in DB2 SQL.

PostPosted: Wed Apr 05, 2017 10:27 pm
by xRDK
Hello,

I'm a stuck and thought I'd post here for advice, I have spent a while on google and I've failed to get anywhere.

I have a field that contains aload of "SUB" characters, I would like to locate fields with this character and replace them with nothing.
Information on the substitute character can be located here; https://en.wikipedia.org/wiki/Substitute_character
The code page of my file is here and the sub is 3F; https://en.wikipedia.org/wiki/EBCDIC_285

I am struggling to do this though, if I do it in hex:

select vcname, replace(hex(VCNAME),'3F','')
from mytable
where hex(VCNAME) like '%3F%'

This works, but I can't get the hex conversion back to a character string...

So I'm wondering if there are any other ideas? Prefereably I'd rather do this in SQL.

Thanks in advance,
Ryan