[Info-ingres] More similar to

[Martin Bowes]

I would like to keep it in the database, but if push comes to shove I'm more than willing to look at some utility outside the database.

Marty

From: Paul White [mailto:paul.white at shift7solutions.com.au]
Sent: 31 August 2018 12:20
To: Martin Bowes; info-ingres at lists.planetingres.org
Subject: RE: [Info-ingres] More similar to

So you want output like this?

res_note for snapshot_id = 14 is dodgy.
pos=55 char=0x03
pos=103 char=0x04
pos=11999 char=0x00

res_note for snapshot_id = 28 is dodgy.


Text processing in the database. yuk.
Do you have the option of extracting the matching records to a file then do the grunt work with text processing tools?

Paul


From: info-ingres-bounces at lists.planetingres.org [mailto:info-ingres-bounces at lists.planetingres.org] On Behalf Of Martin Bowes
Sent: Friday, 31 August 2018 8:41 PM
To: info-ingres at lists.planetingres.org
Subject: [Info-ingres] More similar to

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/22b216f1/attachment.html>