Informix SPL - String Functions

Hi, I am an Oracle Developer, stuck in an Informix world. I am trying to do probably the simplest thing I can think of and cannot get it to work. What I am trying to do is given a string, go through it and find the occurences (what positions in the string) another string has. My questions are..... What type of variable to I need? Varchar, lvarchar?? I do not know how long the string will be each time. Is there string funcitons in Informix (we have the crappiest documentation here) that allow you to access character by character?? I read somewhere that you cannot actually use a variable (ie a counter) when accessing some data types. ie StringName[counter] won't work. Is this true? Or is this whole line of questioning pointless?? Can anyone help?? Mark

Nathan Lee wrote: > I am an Oracle Developer, stuck in an Informix world. Welcome! I guess your going to find out how real DBMSs do it. ;-) First - a Note: > Is there string funcitons in Informix (we have the crappiest documentation > here) that allow you to access character by character?? All of the product's documentation is available for down-load, for free at: http://www.informix.com/answers/english/product1.htm Depending on your version, you're going to find what you need there. > What I am trying to do is given a string, go through it and find the > occurences (what positions in the string) another string has. OK. Depending on your version, there are several ways to handle this. I gather from the way you're worried about lvarchar that you're using IDS.2000 or 9.X. The following script is all hale for IDS.2000 (9.2). It won't work on 9.14 because SUBSTR() wasn't in there, yet. There are several low-level string manipulation primitives in the IFMX SQL/SPL. The really useful one for this task is SUBSTR(), and in the following script I illustrate how to use in, first more or less by itself, and then in conjunction with another ORDBMS feature (COLLECTIONS) that lets you create a tool that you can use in a SQL query rather than depending on SPL. -- -- File: Count_of_Target_in_Source.sql -- -- About: -- -- This script illustrates several ways to address the discovery of substrings in -- some target. This script includes examples of the SUBSTR() function, and of -- how to use COLLECTION types. -- -- NOTE: Arg1 is the Target, Arg2 is the String being searched within. -- CREATE FUNCTION Count_of_Target_in_Source( Arg1 LVARCHAR, Arg2 LVARCHAR )RETURNS INTEGER DEFINE nStrLen INTEGER; DEFINE nTargLen INTEGER; DEFINE cFirst CHAR(1); DEFINE i INTEGER; LET nStrLen = LENGTH ( Arg2 ); LET nTargLen = LENGTH ( Arg1 ); IF ( nTargLen > nStrLen ) THEN RETURN NULL::INTEGER; END IF; LET cFirst = SUBSTR( Arg1, 0); FOR i IN ( 1 TO nStrLen ) IF ( cFirst = SUBSTR( Arg2, i, 1 ) ) THEN IF ( Arg1 = SUBSTR ( Arg2, i, nTargLen ) ) THEN RETURN i WITH RESUME; END IF; END IF; END FOR; END FUNCTION; -- -- This first example will return a series of results for each time that -- you find the target string in the searched string. We illustrate what this looks -- like in the following figure. -- EXECUTE FUNCTION Count_of_Target_in_Source('Foo','BarFoo'); EXECUTE FUNCTION Count_of_Target_in_Source('Foo','BarFooBarFoo');EXECUTE FUNCTION Count_of_Target_in_Source('Foo','BarFooBarFooBarFooBarFooBarFoo'); EXECUTE FUNCTION Count_of_Target_in_Source('Foo','BarFooBarFooBarFooBarFooBarFooBarFooBarFoo'); -- -- This is useful in another SPL function. But it isn't all that useful in SQL - yet. The second -- implementation we introduce below is. It uses the COLLECTION feature to illustrate how -- you can achieve some useful SQL results, too. -- DROP FUNCTION Count_of_Target_in_Source( LVARCHAR, LVARCHAR );-- -- CREATE FUNCTION Count_of_Target_in_Source( Arg1 LVARCHAR, Arg2 LVARCHAR ) RETURNS SET( INTEGER NOT NULL ) DEFINE nStrLen INTEGER; DEFINE nTargLen INTEGER; DEFINE cFirst CHAR(1); DEFINE i INTEGER; DEFINE nCnt INTEGER; DEFINE stIntRet SET( INTEGER NOT NULL ); LET nStrLen = LENGTH ( Arg2 ); LET nTargLen = LENGTH ( Arg1 ); IF ( nTargLen > nStrLen ) THEN RETURN NULL::INTEGER; END IF; LET cFirst = SUBSTR( Arg1, 0); LET nCnt = 0; FOR i IN ( 1 TO nStrLen ) IF ( cFirst = SUBSTR( Arg2, i, 1 ) ) THEN IF ( Arg1 = SUBSTR ( Arg2, i, nTargLen ) ) THEN INSERT INTO TABLE( stIntRet ) VALUES ( i ) ; LET nCnt = nCnt + 1; END IF; END IF; END FOR; IF ( nCnt > 0 ) THEN RETURN stIntRet; END IF; RETURN NULL; END FUNCTION; -- EXECUTE FUNCTION Count_of_Target_in_Source('Foo','BarFoo'); EXECUTE FUNCTION Count_of_Target_in_Source('Foo','BarFooBarFoo');EXECUTE FUNCTION Count_of_Target_in_Source('Foo','BarFooBarFooBarFooBarFooBarFoo'); EXECUTE FUNCTION Count_of_Target_in_Source('Foo','BarFooBarFooBarFooBarFooBarFooBarFooBarFoo'); EXECUTE FUNCTION Count_of_Target_in_Source('Foo','BarFoBarFoBarFoBarFoBarFoBarFoBarFoBarFoBarFo'); -- -- Now, for giggles, here is a set of test data in a test table. -- CREATE TABLE Test_SubString ( Id SERIAL PRIMARY KEY, Val VARCHAR(128) NOT NULL );-- INSERT INTO Test_SubString ( Val ) VALUES ( 'BarFoo'); INSERT INTO Test_SubString ( Val ) VALUES ( 'BarFoBarFoo'); INSERT INTO Test_SubString ( Val ) VALUES ( 'BarFoBarFoBarFoo'); INSERT INTO Test_SubString ( Val ) VALUES ( 'BarFoBarFoBarFoBarFoo'); INSERT INTO Test_SubString ( Val ) VALUES ( 'BarFoBarFoBarFoBarFoBarFoo'); INSERT INTO Test_SubString ( Val ) VALUES ('BarFoBarFoBarFoBarFoBarFoBarFoBarFoBarFoBarFo'); -- -- Obvious stuff. -- SELECT Id, Count_of_Target_in_Source ( 'Foo', T.Val::LVARCHAR) FROM Test_SubString T;-- -- Less obvious stuff. -- SELECT T.Id, T.Val FROM Test_SubString T WHERE 19 IN Count_of_Target_in_Source ( 'Foo', T.Val::LVARCHAR);-- -- Cool, huh? -- -- Cleanup. -- DROP TABLE Test_SubString; DROP FUNCTION Count_of_Target_in_Source ( LVARCHAR, LVARCHAR ); OK. Now for the caveats: First, lvarchar instances have a limited size. In order to get around this you will need to get to text or clob objects, but the same basic idea I outline here should work just fine. Second, this implementation is going to be slower than you might like, for two reasons. It's written using SPL which is not nearly so fast as 'C' (although it is a relatively easy thing to do to re-write this to use 'C'). And it doesn't take advantage of any indexing. If what you have a a text library and you're looking for strings within those documents then your better bet by far (like 1000X, dude) is a document DataBlade. My opinion is that you'll find the IFMX database is a much simpler system to use from the point of view that it has fewer basic concepts to get your head around. The philosophy is to make it far easier to combine these concepts to achieve the desired result. By contrast, O has everything but the kitchen sink, and no really straight-forward way of figuring out what's there. For example, the following is a really useful query for finding out if a UDF exists that you might want to use. Although in this case I retrieve only a single row, you can use this to get all kinds of useful information about the functions that can be applied to various types to transform them, or to operate on them. SELECT P.procname AS ProcName, P.paramtypes::LVARCHAR AS Param_Types, ifx_ret_types ( P.procid )::LVARCHAR AS Return_Type,

Hi, As far as I know, Informix does not allow using variables instead of literal numbers in string functions and expressions like smstr[number] and smstr[number1, number2] which of course makes it difficult to develop wise procedures. Max Nathan Lee wrote: > Hi, > > I am an Oracle Developer, stuck in an Informix world. I am trying to do > probably the simplest thing I can think of and cannot get it to work. > > What I am trying to do is given a string, go through it and find the > occurences (what positions in the string) another string has. > > My questions are..... What type of variable to I need? Varchar, lvarchar?? > I do not know how long the string will be each time. > > Is there string funcitons in Informix (we have the crappiest documentation > here) that allow you to access character by character?? > > I read somewhere that you cannot actually use a variable (ie a counter) when > accessing some data types. ie StringName[counter] won't work. Is this > true? > > Or is this whole line of questioning pointless?? > > Can anyone help?? > > Mark