[Info-ingres] More similar to

[Adrian Williamson]

Hi Marty,

 

I think your pain has a name : Transliteration

 

Roy invented some 'baroque' SQL for a customer recently which did the job
for a varchar(25) to correct a load of issues for soundex_dm computed
values.

 

I'm not sure it would scale to 12,000 as the working part of the SQL Insert
/ select had hard coded ordinal positions for each character.

 

It's the closest I've seen SQL being made to look like some C-Language
gobbledygook.

 

I'll let him explain.

 

Cheers

 

Adrian

 

 

 

From: info-ingres-bounces at lists.planetingres.org
<info-ingres-bounces at lists.planetingres.org> On Behalf Of Martin Bowes
Sent: 31 August 2018 11:41
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/34d8f760/attachment.html>