[Info-ingres] More similar to

[Paul White]

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