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 (AT) iiug (DOT) 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 (AT) gmail (DOT) com> wrote:

On Mar 3, 12:17*am, Art Kagel <art.ka... (AT) gmail (DOT) com> wrote:
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... (AT) gfm (DOT) co.uk> wrote:


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