[Info-ingres] More similar to

[Martin Bowes]

Hi All,

I just knew this non printing character stuff was going to come back and bite me...

I can identify the rows with non printing characters excluding \t, \r and \n.

Now I need to find which characters are being objected to on each row. I may need to know their position as well.

The data in question is a long varchar. But (fortunately) the longest item is under 12000 characters, so we are under the 32000 char varchar conversion limit.

The easiest way I can think of doing this is to do it in a database procedure...

create procedure identify_bad_char
as declare
    snapshot_id integer4 not null not default;
    res_note    long varchar not null not default;
    msgid       integer4 not null not default;
    msg         varchar(256) not null not default;
begin
    msgid = 0;
    FOR SELECT snapshot_id, res_note INTO :snapshot_id, :res_note
        FROM basket_snapshot
        WHERE res_note SIMILAR TO '%[^' + x'09' + x'0a' + x'0d' + '[:print:]]%'
    DO
        msgid = msgid + 1;
        msg = 'res_note for snapshot_id = ' + varchar(:snapshot_id) + ' is dodgy.';
        message :msgid :msg;

        /* Insert manic while loop here and process the line character by character */
    ENDFOR;
end;

Before I start coding up the manic while loop ... does anyone have a suggestion as to how I can do this more efficiently.

The data was probably added to a web form via a cut and paste from a word document, so we've picked up things like smart quotes. Hence we may ultimately be interested in replacing multi character sequences with simple ansi equivalents. I have a suspicion a clean() function is galloping in my direction.

Martin Bowes
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.planetingres.org/pipermail/info-ingres/attachments/20180831/388268a3/attachment.html>