Sybase: how can I remove non-printable characters from CHAR or VARCHAR fields with SQL?
- by Kenny Drobnack
I'm working with a Sybase database that seems to have non-printable characters in some of the string fields and this is throwing off some of our processing code. At first glance, it seemed to only be newlines and carriage returns, but we also have an ASCII code 27 in there - an ESC character, some accented characters, and some other oddities in there.
I have no direct access to change the database, so changing the bad data isn't an option, yet. For now I have to make do with just filtering it out. We're trying to export the table data from one database and load it into a database used by another application in a nightly batch process.
Ideally, I'd like to have a function that I can pass a list of characters and just have Sybase return the data with those characters removed. I'd like to keep it something we could do in plain SQL if possible.
Something like this to remove characters that are ASCII 0 - 31.
select str_replace(FIELD1, (0-31), NULL) as FIELD1,
str_replace(FIELD2, (0-31), NULL) as FIELD2
from TABLE
So far, str_replace is the nearest I can find, but it only allows replacing one string with another. No support for character ranges and won't let me do the above.
We're running on Sybase ASE 12.5 on Unix servers.
