[Info-ingres] weird string processing request

[Roy Hann]

Martin Bowes wrote:

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

If I understand you right, you know the tags in advance, right?

NB I haven't tried this and I can't just at the moment. I have no idea
if I've got the syntax even close. But one idea that occurs to me is to
use the SUBSTRING function with a SIMILAR TO pattern to pick out each
tagN:value, and pass the result to the REPLACE function to remove the
"tagN:" part. You end up with something horrid like:

INSERT INTO tbl ( tag1, tag2, ..., tagN )
SELECT replace(substring(longstr SIMILAR TO 'tag1:[^|]+'),'tag1:',''),
       replace(substring(longstr SIMILAR TO 'tag2:[^|]+'),'tag2:',''),
       ... 
       replace(substring(longstr SIMILAR TO 'tagN:[^|]+'),'tagN','')
FROM wherever

Roy