string function to replace all special ascii characters as ?

[Former Member]

I found this select syntax online but not able to make it work. Please help.

I have a table that have colName as TEXT column that has special ascii characters from 0 thru 31. I an trying to do a select for this colName that will replace all special characters as '?' What am I doing wrong in this select?

select REPLACE(**colName**, 
         SUBSTRING(**colName**, 
            PATINDEX('%[^a-zA-Z0-9 ]%', 
                  **colName** collate Latin1_General_BIN), 1), '?')
from table 
WHERE PATINDEX('%[^a-zA-Z0-9 ]%', **colName** collate Latin1_General_BIN) <> 0

[MarkCulp]

What database software are you using? ... I ask because the 'collate Latin1_General_BIN' syntax is not supported by SQL Anywhere ... so where did you find the syntax online.

Please refer to the SA documentation which you can find at http://dcx.sybase.com - specifically you can find the patindex function here.

[Former Member]

We have Adaptive Server Enterprise.

[Breck_Carter]

This isn't the forum you're looking for...

[VolkerBarth]

Be careful: The Empire HANA Strikes Back...

[MarkCulp]

Take a look at my answer to a similar question.