[Info-ingres] More similar to

[Roy Hann]

Friday, August 31, 2018, 11:59:37 AM, you wrote:

> 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.

It looked like the worst ASCII art ever. But it was as obvious as it
was huge. I am not proud of it, nor will it solve your problem Marty.

What I _am_ proud of is the query I wrote before that, to find what
characters were ever used in a varchar(25) column. (This won't be any use
to you either because your strings are too long for it.)

It brought me two great joys. Joy the first: it worked. I definitely
expected it to work but sometimes...you know...when you get off the
beaten track...

Joy the second: it was really fast. I expected it to be faster than
looping but I didn't expect it to be as fast as it was.

------------------------------8<---------------------------------------
drop if exists session.positions;
declare global temporary table session.positions as
select 1 as position union select 2 union select 3 union
select 4 union select 5 union select 6 union select 7 union
select 8 union select 9 union select 10 union select 11 union
select 12 union select 13 union select 14 union select 15 union
select 16 union select 17 union select 18 union select 19 union
select 20 union select 21 union select 22 union select 23 union
select 24 union select 25
on commit preserve rows
with norecovery;
commit;

drop if exists session.charset_in_use;
declare global temporary table session.charset_in_use as
select distinct charextract(d.surname,p.position) as letter,
                hex(charextract(d.surname,p.position)) as hex_letter
from delegate d cross join session.positions p
on commit preserve rows
with norecovery;
commit;

Roy