从较大字段选择子字段

您可能会有处理包含分隔子字段的消息的消息流。如果您知道子字段的定界符,可以编码 ESQL 从周围的上下文抽取该子字段。

如果您创建执行此任务的函数,或者类似的函数,则可以从 ESQL 模块(Compute、Database 和 Filter 节点中的)和映射文件(DataDelete、DataInsert、DataUpdate、Extract、Mapping 和 Warehouse 节点所使用的)调用它。

以下函数示例抽取了由特定字符分隔的特定消息子字段。

CREATE FUNCTION SelectSubField
       (SourceString CHAR, Delimiter CHAR, TargetStringPosition INT)
       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;	
相关概念
消息流概述
相关任务
设计消息流
定义消息流内容
相关参考
Compute 节点
Database 节点
Filter 节点
ESQL 引用
DECLARE 语句
POSITION 函数
SET 语句
SUBSTRING 函数
TRIM 函数
WHILE 语句
声明 | 商标 | 下载 | 书库 | 支持 | 反馈
Copyright IBM Corporation 1999, 2006 最后一次更新时间:2006/08/14
ac20400_