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.

asked 27 Mar '13, 12:08

hm9m's gravatar image

hm9m
6344
accept rate: 0%

edited 27 Mar '13, 13:54

Mark%20Culp's gravatar image

Mark Culp
23.0k9130270


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.

permanent link

answered 27 Mar '13, 12:21

Mark%20Culp's gravatar image

Mark Culp
23.0k9130270
accept rate: 40%

edited 27 Mar '13, 14:23

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.

(27 Mar '13, 12:40) hm9m
Replies hidden

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.

(27 Mar '13, 12:48) Mark Culp

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

permanent link

answered 06 Jun '14, 10:10

pa001's gravatar image

pa001
11
accept rate: 0%

edited 06 Jun '14, 10:10

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×50
×49
×12

question asked: 27 Mar '13, 12:08

question was seen: 5,834 times

last updated: 06 Jun '14, 10:10