[Info-ingres] weird string processing request

[Martin Bowes]

Hi All,

On Ingres 11

I have just had a request to take a table with a long varchar field which is formatted by a third party to be 'tag1:value1|tag2:value2|....|tagN:valueN' and generate a query which will have the tag names as column headings and the values as indicated.

...Yikes...

The only thing we are given:

1.       Although specified as a long varchar in reality no record is over 550 characters.

2.       each record will have the same tags in the same order, but there may be no associated value.

So far I can't do this in a single simple query, the only thing I've got is to do a copy out copy in as follows:

declare global temporary table stuff as select varchar(stupid_long, 1000) as shorter_string
from ... on commit preserve rows with norecovery;

copy stuff(shorter_string=c0nl) into 'shorter_string.dat';

declare global temporary table stuff2(
tag1varchar(32) not null with default, /* Just a guess on field width, it looks about right...*/
tag2 varchar(32) not null with default,
...
tagN varchar(32) not null with default
) on commit preserve rows with norecovery;

copy stuff2(
dummy='d0:', tag1 = 'c0|',
dummy='d0:', tag2 = 'c0|',
...
dummy='d0:', tagN = c0nl
) from 'shorter_string.dat';
\p\g
select first 20 * from stuff2;
\p\g

That seems to work, but having taken it out of the database onto the file system I could have just written a Perl program to do the deed and the user (God bless their little cotton socks) doesn't want that.

The only other thing I can think of is a raging great statement with lots of locates and lengths and substr statements.
select first 10
cast(substr(shorter,
    locate(shorter, 'tag1:') + length('tag1:'),
    locate(shorter, 'tag2:') - locate(shorter, 'tag1:') - length('tag1:') - 1
) as varchar(32)) as tag1,
...
from (select varchar(stupid_long, 1000) as shorter from ...) x


Anyone got a better idea?

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