Question Re: string function to replace all special ascii characters as ? in Technology Q&A

string function to replace all special ascii characters as ?

Former Member — Mon, 25 Mar 2013 17:00:09 GMT

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

Re: string function to replace all special ascii characters as ?

MarkCulp — Mon, 25 Mar 2013 21:47:55 GMT

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.

Re: string function to replace all special ascii characters as ?

MarkCulp — Mon, 25 Mar 2013 21:51:48 GMT

Take a look at my answer to a similar question.

Re: string function to replace all special ascii characters as ?

Former Member — Tue, 26 Mar 2013 14:49:59 GMT

We have Adaptive Server Enterprise.

Re: string function to replace all special ascii characters as ?

Breck_Carter — Tue, 26 Mar 2013 15:41:40 GMT

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

Re: string function to replace all special ascii characters as ?

VolkerBarth — Wed, 27 Mar 2013 12:18:21 GMT

Be careful: ~~The Empire~~ HANA Strikes Back...