select for TEXT column with Non printable characters

[Former Member]

We have a table with TEXT column that may have non-printable characters. I.e. ascii 0 thru 31 and ascii > 254. I want to run select SQL in Sybase central that will bring result set with all non-printable characters replaces as '?' e.g.

SELECT replace(TEXTColumn, regexp,'?') from TABLE

Here regexp is a range of ascii 0 thru 31 and ascii > 254 Can you provide simple and efficient solution to achieve this? TIA.

[MarkCulp]

You indicated in your previous question that you are using ASE and therefore this is not the correct place to ask this question - this forum is for questions about SQL Anywhere for things related to SQL Anywhere. You need to ask your question on an ASE forum - for example: http://scn.sap.com/community/sybase-adaptive-server-enterprise

---

But for those customers that are using SQL Anywhere, the statement to run in DBISQL (i.e. not Sybase Central) is similar to:

select FilterNonAsciiChars( mycol, '?' )
  from MyTable;

To permanently clean the table column of non-ascii characters, use:

update MyTable
   set mycol = FilterNonAsciiChars( mycol )
 where mycol not regexp '[[:ascii:]]*';

Where FilterNonAsciiChars is defined as:

CREATE FUNCTION FilterNonASCIIChars( in @sInput     long varchar,
                                     in @bad_marker char(1) default '' )
RETURNS long varchar
DETERMINISTIC
BEGIN
    declare @pattern   long varchar;
    declare @iNotValid integer;
    declare @sOutput   long varchar;
    declare @char      char(1);

    set @pattern   = '%[^' || ' !"#$%&''()*+,./0-9:;<=>?@A-Z[\\^_`a-z{|}~-' || ']%';
    set @sOutput   = '';
    set @iNotValid = patindex( @pattern, @sInput );

    while @iNotValid > 0 loop
        // pick up everything to the left of the non numeric
        set @char = substr( @sInput, @iNotValid, 1 );
        if @char = ']' then
            -- patindex cannot not catch the ']' character,
            -- so we need to explicitly ignore it
            set @sOutput = @sOutput || left( @sInput, @iNotValid );
            set @sInput = substr( @sInput, @iNotValid+1 );
        else
            -- allow all chars prior to the one we found
            set @sOutput = @sOutput || left( @sInput, @iNotValid - 1 );

            // strip off the invalid character and any similar chars in the string
            set @sInput = replace( substr( @sInput, @iNotValid+1 ), @char, @bad_marker );
        end if;

        // find the next invalid character
        set @iNotValid = patindex( @pattern, @sInput );
    end loop;

    // return our string plus any remaining chars
    return @sOutput || @sInput;
END;

The @bad_marker second parameter can be used to change which character is used to replace the non-ascii characters. E.g. use FilterNonAsciiChars( mycol, '?' ) if you want to replace the non-ascii characters with '?'.

You will note that FilterNonAsciiChars is similar to the FilterChars function in a previous answer.

[Former Member]

Hi Mark, Thanks for your reply. Just want to understand that search string that can be used in SELECT sql and run in Sybase central will be different in ASE vs SQL Anywhere? Thanks again.

[MarkCulp]

Yes, the language accepted by ASE and SQL Anywhere are similar (i.e. both are TSQL variants) but are different enough that you need to ask ASE experts to get an answer to this specific question. I.e. I could give you an answer that would work for SA but I would doubt that it would work in ASE because of the SA-specific functions that I would use.

[Former Member]

Hi Mark,

I found special characters like: ������ �Y1�0�@�D ��� ��� in one of the tables.

How can I search for these and then delete from the table using SQL queries?

Thanks