IIUG Software Repository Files

  File:   $INFORMIXDIR\extend\Examples\Split\README

  
  This file describes the functionality of the SplitString() and the 
 ISplitString() user-defined functions. These are intended as string
 manipulation routines to help developers working with IDS.2000. There is
 one older routine here, which might prove useful for developers working
 with the 9.14 version of the engine. 

  Contents:
  ~~~~~~~~~~

  This BladeLet (Example\Split) contains two user-defined routines (UDRs) which
 split a single string into a COLLECTION of subvalues based on a delimiter. 
 This is similar to the PERL operator of the same name, and has a similar
 purpose. 

   Split ( LVARCHAR, LVARCHAR ) -> LIST(LVARCHAR NOT NULL )
   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

  The first of these routines is Split ( LVARCHAR, LVARCHAR ). It returns a 
 single COLLECTION (LIST) of LVARCHAR instances which correspond to the 
 sub-tokens in the first argument delimitered by any of the characters in the
 second. For example:

EXECUTE FUNCTION Split('Fe Fii Foo Fummm',' ');

(expression)  LIST{'Fe','Fii','Foo','Fummm'}

EXECUTE FUNCTION Split('Fe Fii Fooo Fummmmm', ' ');

(expression)  LIST{'Fe','Fii','Fooo','Fummmmm'}

EXECUTE FUNCTION Split(' Fe  Fii Fooo Fummmmm', ' ');

(expression)  LIST{'Fe','','Fii','Fooo','Fummmmm'}

EXECUTE FUNCTION Split('   Fe Fii Fooo Fummmmm', ' ');

(expression)  LIST{'Fe','Fii','Fooo','Fummmmm'}

EXECUTE FUNCTION Split('    Fe Fii Fooo Fummmmm', ' ');

(expression)  LIST{'Fe','Fii','Fooo','Fummmmm'}

EXECUTE FUNCTION Split('Fe+Fii Foo.Fummm',' +.');

(expression)  LIST{'Fe','Fii','Foo','Fummm'}

EXECUTE FUNCTION Split('Fe Fii Fooo Fummmmm', ' +.');

(expression)  LIST{'Fe','Fii','Fooo','Fummmmm'}

EXECUTE FUNCTION Split(' Fe+ Fii Fooo-Fummmmm', '_');

(expression)  LIST{' Fe+ Fii Fooo-Fummmmm'}

EXECUTE FUNCTION Split('   Fe Fii Fooo Fummmmm', '!@#$%^&*()_+=-{}[]., ');

(expression)  LIST{'Fe','Fii','Fooo','Fummmmm'}

EXECUTE FUNCTION Split('    Fe Fii Fooo Fummmmm', ' ');

(expression)  LIST{'Fe','Fii','Fooo','Fummmmm'}

   This is a useful UDR because it allows you to access the inner words
 of a string and to break a string up into component parts to insert
 them into tables, for example. Also, this example code illustrates
 how to use COLLECTIONs in 'C' UDRs, and it uses a nifty approach to
 exception management.

   ITERATOR ISPlit ( LVARCHAR, LVARCHAR ) -> LVARCHAR
   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   The second UDR is called ISPlit( LVARCHAR, LVARCHAR ). It is an example
 of a 'C' iterator function. Like SPL functions that RETURN WITH RESUME, a
 'C' iterator returns a series of values. The purpose of this function is
 to allow folk to iterate through the elements of the LIST more efficiently
 than they otherwise would.

   EXECUTE FUNCTION ISplit('Fe Fii Foo Fummm',' ');

(expression)  Fe

(expression)  Fii

(expression)  Foo

(expression)  Fummm

EXECUTE FUNCTION ISplit('Fe Fii Fooo Fummmmm', ' ');

(expression)  Fe

(expression)  Fii

(expression)  Fooo

(expression)  Fummmmm

EXECUTE FUNCTION ISplit(' Fe  Fii Fooo Fummmmm', ' ');

(expression)  Fe

(expression)  Fii

(expression)  Fooo

(expression)  Fummmmm

EXECUTE FUNCTION ISplit('   Fe Fii Fooo Fummmmm', ' ');

(expression)  Fe

(expression)  Fii

(expression)  Fooo

(expression)  Fummmmm

EXECUTE FUNCTION ISplit('Fe+Fii Foo.Fummm',' +.');

(expression)  Fe

(expression)  Fii

(expression)  Foo

(expression)  Fummm

EXECUTE FUNCTION ISplit('Fe Fii Fooo Fummmmm', ' +.');

(expression)  Fe

(expression)  Fii

(expression)  Fooo

(expression)  Fummmmm

EXECUTE FUNCTION ISplit('   Fe Fii Fooo Fummmmm', '!@#$%^&*()_+=-{}[]., ');

(expression)  Fe

(expression)  Fii

(expression)  Fooo

(expression)  Fummmmm

EXECUTE FUNCTION ISplit('    Fe Fii Fooo Fummmmm', ' ');

(expression)  Fe

(expression)  Fii

(expression)  Fooo

(expression)  Fummmmm

    To give you an idea of how this might be useful, we present the following
 SPL FUNCTION. Don't worry too much about the SQL. Just focus on what the
 inner loop is doing.

CREATE FUNCTION Test_ISplit ( Arg1 INTEGER, Arg2 LVARCHAR )
RETURNS INTEGER;

  DEFINE lvIntVal   LVARCHAR;
  DEFINE lvIntIntVal LVARCHAR;
  DEFINE nCnt          INTEGER;
  DEFINE nRetVal     INTEGER;

  LET nRetVal = 0;

  FOREACH SELECT T.Val INTO lvIntVal FROM Test_Stuff T  WHERE Id < 100
     LET nCnt = 0;
--
--  This inner loop iterates over the sub-strings within a string, 
--  and counts the ones where the substring contains the second
--  argument. This has the effect of counting how many times the
--  argument string appears in the column, rather than (for example)
--  the number of rows where the argument string appears at all
--  (which might be done using LIKE in a query).
--

     FOREACH EXECUTE FUNCTION ISplit (lvIntVal, ' ' ) INTO lvIntIntVal 

          IF ( lvIntIntVal LIKE '%' || Arg2 || '%' ) THEN
             LET nRetVal = nRetVal + 1;
          END IF;

     END FOREACH;

  END FOREACH;
  RETURN nRetVal;
END FUNCTION;
--
EXECUTE FUNCTION Test_ISplit ( 2, 'the');

   SubLvarchar ( LVARCHAR, INTEGER, INTEGER ) -> LVARCHAR
   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   This is simply a function equivalent for the SUBSTRING() expression. It is
 useful in pre 9.2 engines, where SUBSTRING() doesn't exist.


  EXECUTE FUNCTION SubLvarchar ( '123456789', 2, 5 );

   is equivalent to:

  SELECT SUBSTRING ( '123456789' FROM 2 TO 5 )
    FROM TABLE(SET{1});


   How to Install and Use:
   ~~~~~~~~~~~~~~~~~~~~~~~

    1. Install all of the directories under this root directory into a 
       directory named $INFORMIXDIR\extend\Examples. Call this directory
       Split. Simply unpacking the wad in the righ place ought to do the
       trick.

        Do this because the CREATE FUNCTION statements will look for a shared 
       library named $INFORMIXDIR\extend\Examples\Split\bin\Split.bld

    2. Compile the stuff. There are Makefiles here for Unix and NT.

        UNIX: cd $INFORMIXDIR\extend\Examples\Split\src
              make

        NT:   cd %INFORMIXDIR%\extend\Examples\Split\src
              nmake /f WinNT.mak

    3. These will build a shared object binary named Split.bld and place it
       into $INFORMIXDIR\extend\Examples\Split\bin

    4. In the Database which is your target, run the script found in
       $INFORMIXDIR\extend\Examples\Split\install\register.sql

       This creates all of the functions and will check to see that the
       binary files are in the right places.

    5. If you make any changes, look at the test script in 
       $INFORMIXDIR\extend\Examples\Split\test\test.sql. You're free to add
       anything you like to this, but of course I would appreciate the 
       changes too.

       But if your change breaks this stuff, or fails to include some minimal
       sanity testing, I will find it *really* hard to find the time to
       get the stuff in.

   Feedback:
   ~~~~~~~~~

    I'd love to hear it:

           paul.brown@informix.com