Sybase: how can I remove non-printable characters from CHAR or VARCHAR fields with SQL?

Question

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.

Answer 1

Something like this works in SQL Server, which uses T-SQL like Sybase:

while @@ROWCOUNT > 0
    UPDATE TheTable
    SET strColumn = REPLACE(strColumn, SUBSTRING(strColumn, 
        PATINDEX('%[^a-zA-Z0-9 ]%', 
        strColumn collate Latin1_General_BIN), 1), '')
    WHERE PATINDEX('%[^a-zA-Z0-9 ]%', 
        strColumn collate Latin1_General_BIN) <> 0

The patindex function at least appears to exist on Sybase.

The collation is required to match binary; otherwise [a] would match 'á'.