-- вспомогательная функция ( в общем случае мне нужно обрезать  единый разделитель состоящий из нескольких разных символов,
-- пробел является часным случаем
CREATE FUNCTION VFP.MYLTRIM
(cExpressionSearched VARCHAR( 4000 ), cSearchExpression VARCHAR( 256 ))
RETURNS VARCHAR( 4000 )
SPECIFIC  MYLTRIM
DETERMINISTIC NO EXTERNAL ACTION CONTAINS SQL
BEGIN ATOMIC
    DECLARE   i, len, lencSearchExpression  SMALLINT;
    DECLARE cLtrimString VARCHAR( 4000 );
    SET  i  =   1 ,  len  =  LENGTH(cExpressionSearched),  lencSearchExpression  =  LENGTH(cSearchExpression) ;
    IF  lencSearchExpression =  1  and ASCII(cSearchExpression) =  32  THEN
       SET cLtrimString  = LTRIM(cExpressionSearched);
    ELSE
     IF  lencSearchExpression >  0  THEN
         IF  len >  0  THEN
      	    BOUCLE: WHILE i <= len   DO
     	      IF  (i + lencSearchExpression -  1 ) > len OR LOCATE(cSearchExpression, SUBSTR(cExpressionSearched, i, lencSearchExpression)) =  0  THEN
	            LEAVE  BOUCLE;
      	      END IF;
              SET   i =   i  +  lencSearchExpression;
            END WHILE;
           SET cLtrimString = RIGHT(cExpressionSearched, len - i +  1 ) ;
         ELSE
          SET cLtrimString = '';
        END IF;
      ELSE
        SET cLtrimString = cExpressionSearched;
      END IF;
   END IF;
  RETURN cLtrimString;
END

CREATE FUNCTION VFP.GETALLWORDS2
(cString1 VARCHAR( 4000 ), cStringSplitting1 VARCHAR( 256 ))
RETURNS table (WORDNUM SMALLINT, WORD VARCHAR( 4000 ), STARTOFWORD SMALLINT, LENGTHOFWORD SMALLINT)
SPECIFIC GETALLWORDS2
DETERMINISTIC NO EXTERNAL ACTION CONTAINS SQL
-- GETALLWORDS2() User-Defined Function Inserts the words from a string into the table.
 -- GETALLWORDS2( cString[,  cStringSplitting])
 -- Parameters
 -- cString   varchar(4000) - Specifies the string whose words will be inserted into the table  GETALLWORDS2.
 -- cStringSplitting  varchar(256) - Optional. Specifies the string used to separate words in  cString.
 -- The default delimiter is space.
 -- Note that GETALLWORDS2( ) uses   cStringSplitting as a single delimiter.
 -- Return Value table
 -- Remarks GETALLWORDS2() by default assumes that words are delimited by space. If you specify another string as delimiter, this function ignores spaces and uses only the specified string.
 -- Example
 -- SELECT WORD FROM TABLE(VFP.GETALLWORDS2('We hold these truths to be self-evident,
 that all men are created equal, that they are endowed by their Creator with certain unalienable Rights, that among these are Life, Liberty
 and the pursuit of Happiness.')) AS A where WORDNUM = 30 ;  -- Displays 'Liberty'
 -- SELECT WORDNUM  FROM TABLE(VFP.GETALLWORDS2('SQL Procedural Language DB2')) AS A ORDER BY WORDNUM DESC FETCH FIRST 1 ROW ONLY; -- Displays 4
 -- See Also GETWORDNUM() , GETWORDCOUNT() ,  GETALLWORDS()  User-Defined Functions
BEGIN ATOMIC
    DECLARE nEndString, nLenSrtingSplitting, BegOfWord SMALLINT;
    DECLARE cString  VARCHAR( 4000 );
    DECLARE cStringSplitting VARCHAR( 256 );
    SET  cString = cString1,  cStringSplitting = cStringSplitting1, BegOfWord =  1  ;
    SET  cString  =  COALESCE(cString, ''), cStringSplitting  =  COALESCE(cStringSplitting, ' '), nLenSrtingSplitting  =  LENGTH(cStringSplitting), nEndString  =  1  + LENGTH(cString);

    IF nLenSrtingSplitting >  0   THEN
               WHILE BegOfWord < (nEndString - nLenSrtingSplitting +  1  )  AND  LOCATE(SUBSTR(cString, BegOfWord, nLenSrtingSplitting), cStringSplitting) >  0      DO
                     SET   BegOfWord =   BegOfWord  +  nLenSrtingSplitting; --  skip break strings, if any
               END WHILE;
               SET  cString = SUBSTR(cString, BegOfWord);
    END IF;
RETURN WITH
   TEMP1 ( STRING) AS (VALUES (cString) ),
   TEMP2 ( WORDNUM, STARTOFWORD, WORD, STRING_LEFT) AS
   (SELECT  SMALLINT( 1 ), BegOfWord,
   SUBSTR(STRING, 1 , CASE WHEN LOCATE(cStringSplitting, STRING) =  0  OR nLenSrtingSplitting =  0  THEN LENGTH(STRING) ELSE LOCATE(cStringSplitting,STRING) -  1  END),
   CASE WHEN nLenSrtingSplitting =  0  THEN '' ELSE VFP.MYLTRIM(SUBSTR(STRING, CASE LOCATE(cStringSplitting, STRING) WHEN   0  THEN LENGTH(STRING) + nLenSrtingSplitting ELSE LOCATE(cStringSplitting,STRING) END), cStringSplitting)  END
   FROM TEMP1 WHERE STRING <> ''
   UNION ALL
     SELECT WORDNUM +  1 ,  nEndString - length(STRING_LEFT),
     SUBSTR(STRING_LEFT, 1 , CASE LOCATE(cStringSplitting,STRING_LEFT) WHEN  0  THEN LENGTH(STRING_LEFT) ELSE LOCATE(cStringSplitting,STRING_LEFT) -  1  END),
     VFP.MYLTRIM(SUBSTR(STRING_LEFT, CASE LOCATE(cStringSplitting,STRING_LEFT) WHEN  0  THEN LENGTH(STRING_LEFT) + nLenSrtingSplitting ELSE LOCATE(cStringSplitting,STRING_LEFT) END), cStringSplitting)
     FROM TEMP2 WHERE STRING_LEFT <> '' )
  SELECT WORDNUM, WORD, STARTOFWORD, LENGTH(WORD) FROM TEMP2 ORDER BY  1 ;
END

CREATE FUNCTION VFP.GETALLWORDS
(cString1 VARCHAR( 4000 ), cDelimiters VARCHAR( 256 ))
RETURNS table (WORDNUM SMALLINT, WORD VARCHAR( 4000 ), STARTOFWORD SMALLINT, LENGTHOFWORD SMALLINT)
SPECIFIC  GETALLWORDS
DETERMINISTIC NO EXTERNAL ACTION MODIFIES SQL DATA
-- GETALLWORDS() User-Defined Function Inserts the words from a string into the table.
 -- GETALLWORDS( cString[,  cDelimiters])
 -- Parameters
 -- cString   varchar(4000) - Specifies the string whose words will be inserted into the table  GETALLWORDS. 
 -- cDelimiters  varchar(256) - Optional. Specifies one or more optional characters used to separate words in  cString.
 -- The default delimiters are space, tab, carriage return, and line feed. Note that GETALLWORDS( ) uses each of the characters in  cDelimiters as individual delimiters, not the entire string as a single delimiter. 
 -- Return Value table 
 -- Remarks GETALLWORDS() by default assumes that words are delimited by spaces or tabs. If you specify another character as delimiter, this function ignores spaces and tabs and uses only the specified character.
 -- Example
 -- SELECT *  FROM TABLE(VFP.GETALLWORDS('The default delimiters are space, tab, carriage return, and line feed.
 If you specify another character as delimiter, this function ignores spaces and tabs and uses only the specified character.')) AS A;
 -- See Also GETWORDNUM() , GETWORDCOUNT() User-Defined Functions 
BEGIN ATOMIC
     DECLARE cCharSplitting VARCHAR( 1 ) DEFAULT '';
     DECLARE cString  VARCHAR( 4000 );
     SET  cString = cString1;
     IF  LENGTH(cDelimiters) >  0   THEN
        IF LOCATE(SPACE( 1 ), cDelimiters) >  0  THEN
            SET cCharSplitting = SPACE( 1 );
        ELSE
            SET cCharSplitting = LEFT(cDelimiters, 1 ) ;
        END IF;
        SET cString = TRANSLATE(cString, REPEAT(cCharSplitting,  LENGTH(cDelimiters)), cDelimiters);
     END IF;

   RETURN SELECT WORDNUM, WORD, STARTOFWORD, LENGTHOFWORD  FROM TABLE(VFP.GETALLWORDS2(cString, CAST(cCharSplitting AS VARCHAR( 256 )))) AS GETALLWORDS;
END