DataStage常用函数大全.doc

DataStage 常用函数大全

DATASTAGE 常用函数大全................................................................................................ 1

一、 类型转换函数......................................................................................................................... 4











$%&

$%&

$%&

'(!)*

'(

+

+*





'(







,

二、 字符串函数........................................................................................................................... 17

*!)

*

1-39

.



(

0)

&

$&#

12

1

!)

3&

'

)&#



1&

,

三、 数字函数.............................................................................................................................. 25

*)5

*

*$

67

67

四、 日期和时间函数................................................................................................................... 27



9)8

2-39

:;48

8

6&&

6)

68

6

!#.>&8

!.>&8

3/).>&8

&

&





:;48

:;48&



.>&8

<&8

<

<(>

五、 Null 处理函数....................................................................................................................... 38

$!!)

$!)

3-39

以下函数位于表达式编辑器的“类型转换”类别中。方括号表示参数是可选的。缺省日期格式为 %yyyy-

%mm-%dd。

以下示例按照 Transformer 阶段的“派生”字段中所示来显示这些函数。

根据其数字代码值生成一个 ASCII 字符。您可以指定 allow8bits 参数来转换 8 位 ASCII 值（可

选）。

 输入：code (number)，[allow8bits]

 输出：result (char)

 示例。以下示例将 ASCII 码 65 输出为字符 A。

以下示例会将 mylink.mydate 列中包含的日期输出为 dd:mm:yyyy 格式的字符串。如果

mylink.mydate 包含日期 2009 年 8 月 18 日，那么输出字符串为“18:08:2009”：

将给定的日期返回为压缩十进制值。如果您的目标十进制数指定了小数位，那么部分日期会在小

数点后显示。您可以指定格式字符串来指定如何以十进制数形式存储日期（可选）。缺省格式字

符串为“%yyyy%mm%dd”，因此，日期 2009-08-25 将存储为十进制数 20090825。格式字符串

只能指定包含数字的格式。例如，不能指定诸如“%yyyy-%mm-%dd”之类的格式字符串，这是因

为压缩十进制值中不能存储连字符 (-)。以下标记对于与十进制值之间的转换有效：

%yyyy（四位数表示的年份）

%yy（两位数表示的年份）

4-39

 输出：converted_date (decimal)

 示例。如果 mylink.basedate 列包含日期 2012-08-18，那么以下函数会将日期存储为

十进制数 18082012：

如果 mylink.basedate 列包含日期 2012-08-18，并且目标列的长度为 10，小数位为

2，那么以下函数会将日期存储为十进制数 201208.18：

将给定的压缩十进制数返回为日期。在将十进制数转换为日期时，会忽略其符号和小数位。您可

以指定格式字符串来指定如何以十进制数形式存储日期（可选）。缺省格式字符串为“%yyyy

%mm（两位数表示的月份）

%dd（两位数表示的一月中的第几天）

%ddd（三位数表示的一年中的第几天）

字面值数字 0 到 9 都有效。

 输入：basedec (decimal) [, format (string)]

 输出：date

 示例。如果 mylink.mydecdata 列包含值 18082012，那么以下函数将返回日期 2012-

08-18：

5-39