Selecting a subfield from a larger field

If you create a function that performs this task, or a similar one, you can invoke it both from ESQL modules (for Compute, Database, DatabaseInput, and Filter nodes) and from mapping files (used by the Mapping node).

The following function example extracts a particular subfield of a message that is delimited by a specific character.

CREATE FUNCTION SelectSubField 
       (SourceString CHAR, Delimiter CHAR, TargetStringPosition INT) 
d       RETURNS CHAR
-- This function returns a substring at parameter position TargetStringPosition within the      
-- passed parameter SourceString.  An example of use might be:
-- SelectSubField(MySourceField,' ',2) which will select the second subfield from the
-- field MySourceField delimited by a blank.  If MySourceField has the value
-- "First Second Third" the function will return the value "Second"
  BEGIN
    DECLARE DelimiterPosition INT;
    DECLARE CurrentFieldPosition INT 1;
    DECLARE StartNewString INT 1;
    DECLARE WorkingSource CHAR SourceString;
    SET DelimiterPosition = POSITION(Delimiter IN SourceString);
    WHILE CurrentFieldPosition < TargetStringPosition
     	DO
     		IF DelimiterPosition = 0  THEN
   			-- DelimiterPosition will be 0 if the delimiter is not found 
         -- exit the loop
     			SET CurrentFieldPosition = TargetStringPosition;
     		ELSE
     			SET StartNewString = DelimiterPosition + 1;
     			SET WorkingSource = SUBSTRING(WorkingSource FROM StartNewString);
     			SET DelimiterPosition = POSITION(Delimiter IN WorkingSource);
     			SET CurrentFieldPosition = CurrentFieldPosition + 1;
     		END IF;
    END WHILE;
    IF DelimiterPosition> 0 THEN
       -- Remove anything following the delimiter from the string
       SET WorkingSource = SUBSTRING(WorkingSource FROM 1 FOR DelimiterPosition);     	
       SET WorkingSource = TRIM(TRAILING Delimiter FROM WorkingSource);
    END  IF;  
    RETURN WorkingSource;
END;