Before I start battering my head against the wall (or resort to Delphi)  has anyone a way to extract a row from a CLOB holding structured list?

eg

TVDB:123456
IMDB:tt159753
TMDB:789456

and I want the IMDB row

Actually I only want the tt159753 but that's trivial once I get the row.

Roy Lambert

On 5/25/2015 9:49 AM, Roy Lambert wrote:
> Before I start battering my head against the wall (or resort to Delphi)  has anyone a way to extract a row from a CLOB holding structured list?
>
> eg
>
> TVDB:123456
> IMDB:tt159753
> TMDB:789456
>
> and I want the IMDB row
>
> Actually I only want the tt159753 but that's trivial once I get the row.
>
> Roy Lambert
>


How about something like this (i'm using ';' as delimiter but you can
obviously replace with CRLF):

select SUBSTRING( SUBSTRING(MyCLOB, POSITION('IMDB' in MyCLOB))  , 1 FOR
POSITION( ';' in SUBSTRING(MyCLOB, POSITION('IMDB' in MyCLOB)))-1) from
MyTable

Raul

Raul,

> How about something like this (i'm using ';' as delimiter but you can
> obviously replace with CRLF):
>
> select SUBSTRING( SUBSTRING(MyCLOB, POSITION('IMDB' in MyCLOB))  , 1 FOR
> POSITION( ';' in SUBSTRING(MyCLOB, POSITION('IMDB' in MyCLOB)))-1) from
> MyTable

Not bad...

Uli

Raul

Thanks. What I came up with (after much mistyping of brackets) was something very similar to yours but with a check in case the IMDB line was the only one there

IF(POS(#13,_WebDB)>0 ,
SUBSTR(SUBSTR(_WebDB,LENGTH('IMDB:')+POS('IMDB:',_WebDB),Length(_WebDB)),1,
POS(#13, SUBSTR(_WebDB,LENGTH('IMDB:')+POS('IMDB:',_WebDB),Length(_WebDB))))
,
SUBSTR(_WebDB,LENGTH('IMDB:')+POS('IMDB:',_WebDB),Length(_WebDB)))


Isn't it horrible, but it does work.

Roy Lambert

On 5/25/2015 10:38 AM, Roy Lambert wrote:
> Raul
>
> Thanks. What I came up with (after much mistyping of brackets) was something very similar to yours but with a check in case the IMDB line was the only one there
>
> IF(POS(#13,_WebDB)>0 ,
> SUBSTR(SUBSTR(_WebDB,LENGTH('IMDB:')+POS('IMDB:',_WebDB),Length(_WebDB)),1,
> POS(#13, SUBSTR(_WebDB,LENGTH('IMDB:')+POS('IMDB:',_WebDB),Length(_WebDB))))
> ,
> SUBSTR(_WebDB,LENGTH('IMDB:')+POS('IMDB:',_WebDB),Length(_WebDB)))
>
>
> Isn't it horrible, but it does work.


Great,

I realized there are few other scenarios including no IMDB line at all,
IMDB as the only line or IMDB as last line (without delimiter) so my
revised version was this one :

select  IF( (POSITION('IMDB' in MyCLOB)=0),'',SUBSTRING(
SUBSTRING(MyCLOB, POSITION('IMDB' in MyCLOB))  , 1 FOR IF( (POSITION(
';' in SUBSTRING(MyCLOB, POSITION('IMDB' in MyCLOB)))=0),99,POSITION(
';' in SUBSTRING(MyCLOB, POSITION('IMDB' in MyCLOB)))) -1)) from MyTable


Raul

Raul


I'll try that but I decided to create a function

CREATE FUNCTION "ExtractLine" (IN "Field" CLOB COLLATE "ANSI_CI", IN "Required" VARCHAR COLLATE "ANSI_CI", IN "Chop" BOOLEAN)
RETURNS VARCHAR COLLATE "ANSI_CI"
BEGIN

DECLARE Output VARCHAR;
DECLARE LineStart  VARCHAR;
DECLARE Block VARCHAR;

SET LineStart = Required + ':';

IF (Field IS NULL) OR (0 = POS(LineStart,Field)) THEN
SET Output = NULL;
ELSE
SET Block = SUBSTR(Field,POS(LineStart,Field),LENGTH(Field));
IF 0 = POS(#13,Block) THEN
SET Output = Block;
ELSE
SET Output = SUBSTR(Block,1,POS(#13,Block));
END IF;
END IF;

IF ((Chop IS NULL) OR Chop) AND (OUTPUT IS NOT NULL) THEN
SET Output = SUBSTR(Output, LENGTH(LineStart),LENGTH(Output));
END IF;

RETURN Output;
END



Roy Lambert

Raul


Tried it - fail

If the wanted value is part way down the list (eg number 2 of 3) you get 2 & 3 returned. It was getting complex which is why I decided to go the function route.

Roy Lambert

Raul


And the final answer for use in a computed column is

CASE
WHEN 0 <> POS(#13,_WebDB) THEN
IF( 0 = POS(#13, SUBSTR(_WebDB,LENGTH('IMDB:')+POS('IMDB:',_WebDB),Length(_WebDB)))
 THEN  SUBSTR(_WebDB,LENGTH('IMDB:')+POS('IMDB:',_WebDB),Length(_WebDB))
 ELSE SUBSTR(SUBSTR(_WebDB,LENGTH('IMDB:')+POS('IMDB:',_WebDB),Length(_WebDB)),1,POS(#13,SUBSTR(_WebDB,LENGTH('IMDB:')+POS('IMDB:',_WebDB),Length(_WebDB)))))
WHEN POS('IMDB:',_WebDB)<>0 THEN SUBSTR(_WebDB,LENGTH('IMDB:')+POS('IMDB:',_WebDB),Length(_WebDB))
ELSE NULL
END

There are days when I hate SQL and days when I REALLY hate SQL - guess which this is


Roy Lambert