Simple String Manipulation ~ Answered: amber (solid confidence) — Nog supplies a complete, correct SPL stored procedure to strip text before a delimiter; technically correct but the asker cannot use it because the server is read-only for them, so they fall back to client-side code.

Advisory only.

I'm pretty new to informix and I can't find out how to do this.. I'm try to just get everything before the ' [ ' symbol. My Cell Data: 5500 Front Street[Joes Bar and Grill I want to be: 5500 Front Street I thought I could use the TRIM function, but I failed. Thanks, James

OK, do you always know the position of the bracket within the string or do you have to search for it ala the 'C' strstr() function? The former is doable with the substr() function, the latter, you'll have to write a UDR (User Defined Routine) in either 'C' or Java and add it to the engine. Not hard to do. Another option would be to post-process the data after extraction if you are outputting it to a file for later use or to a host language program. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) See you at the 2010 IIUG Informix Conference April 25-28, 2010 Overland Park (Kansas City), KS www.iiug.org/conf Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, Mar 2, 2010 at 7:01 PM, J. Hart <unleashedmaniac@gmail.com> wrote: > I'm pretty new to informix and I can't find out how to do this.. > > I'm try to just get everything before the ' [ ' symbol. > > My Cell Data: 5500 Front Street[Joes Bar and Grill > I want to be: 5500 Front Street > > I thought I could use the TRIM function, but I failed. > > Thanks, > > James > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list >

On Mar 3, 12:17 am, Art Kagel <art.ka...@gmail.com> wrote: > OK, do you always know the position of the bracket within the string or do > you have to search for it ala the 'C' strstr() function? > > The former is doable with the substr() function, the latter, you'll have to > write a UDR (User Defined Routine) in either 'C' or Java and add it to the > engine.  Not hard to do. > > Another option would be to post-process the data after extraction if you are > outputting it to a file for later use or to a host language program. > > Art > > Art S. Kagel > Advanced DataTools (www.advancedatatools.com) > IIUG Board of Directors (a...@iiug.org) > > See you at the 2010 IIUG Informix Conference > April 25-28, 2010 > Overland Park (Kansas City), KSwww.iiug.org/conf > > Disclaimer: Please keep in mind that my own opinions are my own opinions and > do not reflect on my employer, Advanced DataTools, the IIUG, nor any other > organization with which I am associated either explicitly, implicitly, or by > inference.  Neither do those opinions reflect those of other individuals > affiliated with any entity with which I am affiliated nor those of the > entities themselves. > > On Tue, Mar 2, 2010 at 7:01 PM, J. Hart <unleashedman...@gmail.com> wrote: > > I'm pretty new to informix and I can't find out how to do this.. > > > I'm try to just get everything before the ' [ ' symbol. > > > My Cell Data:  5500 Front Street[Joes Bar and Grill > > I want to be: 5500 Front Street > > > I thought I could use the TRIM function, but I failed. > > > Thanks, > > > James > > _______________________________________________ > > Informix-list mailing list > > Informix-l...@iiug.org > >http://www.iiug.org/mailman/listinfo/informix-list HTH CREATE PROCEDURE chopper(PV_Address CHAR(60)) RETURNING CHAR(60) DEFINE LV_Loop SMALLINT; DEFINE LV_Len SMALLINT; DEFINE LV_Char CHAR(1); DEFINE LV_Return CHAR(60); ON EXCEPTION RETURN "Failed!"; END EXCEPTION; LET LV_Len = 1; LET LV_Return = PV_Address[1]; FOR LV_Loop = 2 TO LENGTH(PV_Address) LET LV_Char = SUBSTR(PV_Address,LV_Loop,1); IF LV_Char = "[" THEN EXIT FOR; END IF; LET LV_Return = SUBSTR(LV_Return,1,LV_Len) || LV_Char; LET LV_Len = LV_Len + 1; END FOR; RETURN LV_Return; END PROCEDURE;

On Mar 2, 11:40 pm, Nog <n...@gfm.co.uk> wrote: > On Mar 3, 12:17 am, Art Kagel <art.ka...@gmail.com> wrote: > > > > > > > OK, do you always know the position of the bracket within the string or do > > you have to search for it ala the 'C' strstr() function? > > > The former is doable with the substr() function, the latter, you'll have to > > write a UDR (User Defined Routine) in either 'C' or Java and add it to the > > engine.  Not hard to do. > > > Another option would be to post-process the data after extraction if you are > > outputting it to a file for later use or to a host language program. > > > Art > > > Art S. Kagel > > Advanced DataTools (www.advancedatatools.com) > > IIUG Board of Directors (a...@iiug.org) > > > See you at the 2010 IIUG Informix Conference > > April 25-28, 2010 > > Overland Park (Kansas City), KSwww.iiug.org/conf > > > Disclaimer: Please keep in mind that my own opinions are my own opinions and > > do not reflect on my employer, Advanced DataTools, the IIUG, nor any other > > organization with which I am associated either explicitly, implicitly, or by > > inference.  Neither do those opinions reflect those of other individuals > > affiliated with any entity with which I am affiliated nor those of the > > entities themselves. > > > On Tue, Mar 2, 2010 at 7:01 PM, J. Hart <unleashedman...@gmail.com> wrote: > > > I'm pretty new to informix and I can't find out how to do this.. > > > > I'm try to just get everything before the ' [ ' symbol. > > > > My Cell Data:  5500 Front Street[Joes Bar and Grill > > > I want to be: 5500 Front Street > > > > I thought I could use the TRIM function, but I failed. > > > > Thanks, > > > > James > > > _______________________________________________ > > > Informix-list mailing list > > > Informix-l...@iiug.org > > >http://www.iiug.org/mailman/listinfo/informix-list > > HTH > > CREATE PROCEDURE chopper(PV_Address CHAR(60)) > RETURNING CHAR(60)> >     DEFINE  LV_Loop     SMALLINT; >     DEFINE  LV_Len      SMALLINT; >     DEFINE  LV_Char     CHAR(1); >     DEFINE  LV_Return   CHAR(60); > >     ON EXCEPTION >             RETURN "Failed!"; >     END EXCEPTION; > >     LET LV_Len    = 1; >     LET LV_Return = PV_Address[1]; > >     FOR LV_Loop = 2 TO LENGTH(PV_Address) >         LET LV_Char = SUBSTR(PV_Address,LV_Loop,1); >         IF LV_Char = "[" THEN EXIT FOR; END IF; >         LET LV_Return = SUBSTR(LV_Return,1,LV_Len) || LV_Char; >         LET LV_Len = LV_Len + 1; >     END FOR; > >     RETURN LV_Return; > > END PROCEDURE;- Hide quoted text - > > - Show quoted text - Thanks for the procedure. The informix box we are accessing is readonly and we don't have access to add procedures. Got to love outside vendors. I think I might just have to do this on the application side. James