Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 8 of 8 total |
Extracting row from TStringList like CLOB |
Mon, May 25 2015 9:49 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Mon, May 25 2015 10:00 AM | Permanent Link |
Raul Team Elevate | 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 |
Mon, May 25 2015 10:09 AM | Permanent Link |
Uli Becker | 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 |
Mon, May 25 2015 10:38 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Mon, May 25 2015 10:47 AM | Permanent Link |
Raul Team Elevate | 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 |
Mon, May 25 2015 12:39 PM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Mon, May 25 2015 12:43 PM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Wed, May 27 2015 6:10 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
This web page was last updated on Monday, April 29, 2024 at 05:23 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |