Datastage STRING Functions

 Datastage STRING Functions

AlNum

Description:

Return whether the given string consists of alphanumeric characters

Syntax:

AlNum (%string%)

Example:

STRING RESULT

“ABC!@” non-alnum

“ABC123″ alnum

“ABCVF” alnum

“123@#$” non-alnum

“12345″ alnum

“!@#$$” non-alnum

Alpha

Description:

Returns 1 if string is purely alphabetic

Syntax:

Alpha (%string%)

Example:

STRING RESULT

“ABC!@” non-alpha

“ABC123″ non-alpha

“ABCVF” alpha

“123@#$” non-alpha

“12345″ non-alpha

“!@#$$” non-alpha

CompactWhiteSpace

Description:

Return the string after reducing all consecutive whitespace to a single space

Syntax:

CompactWhiteSpace(%string%)

Example:

CompactWhiteSpace(“I Love u “) = “I Love u”

Compare

Description:

Compares two strings for sorting

Syntax:

Compare (%string1%,%string2%,[%justification%])

string1, string2 are the strings to be compared.

Justification is either L for left-justified comparison or R for right -justified comparison

Default: L

OTHER than L or R causes a run-time warning 0 is returned.

The result of the comparison is returned as one of the following values:

-1 string1 is less than string2

0 string1 equals string2 or the justification expression is not valid

1 string1 is greater than string2

Example:

Compare(“data”,”data”,”L”) = 0

Compare(“data”,”data”,”R”) = 0

Compare(“100″,”50″,”L”) = -1

Compare(“100″,”50″,”R”) = 1

Compare(“100B”,”99A”,”L”) = -1

Compare(“100B”,”99A”,”R”) = 1

ComparNoCase

Description:

Case insensitive comparison of two strings

Syntax:

CompareNoCase(%string1%,%string2%)

Example:

CompareNumNoCase(“Ask”,”asked”,2) = 0

ComparNum

Description:

Compare the first n characters of the two strings

Syntax:

CompareNum(%string1%,%string2%,%length%)

Example:

CompareNum(“ask”,”asked”,2) = 0

CompareNum(“ask”,”asked”,4) = -1

CompareNumNoCase

Description:

Caseless comparison of the first n characters of the two strings

Syntax:

CompareNumNoCase(%string1%,%string2%,%length%)

Example:

CompareNumNoCase (“ask”,”Asked”,2) = 0

CompareNumNoCase (“ask”,”Asked”,4) = -1

Convert

Description:

Converts specified characters in a string to designated replacement characters

Syntax:

Convert(%fromlist%,%tolist%,%expression%)

Example:

Convert(“1234567890″,” “,”Data12stage567″)=Datastage

Convert(“ab”,”12″,”ab3456″)= 123456

Here a=1 and b=2 , All “a” replace by 1 and “b” replace by 2

Count

Description:

Count number of times a substring occurs in a string

Syntax:

Count(%string%,%substring%)

Example:

Count(“Datastage”,”a”) = 3

Dcount

Description:

Count number of delimited fields in a string.

Syntax:

DCount(%string%,%delimiter%)

Example:

DCount(“I_am_Data_stage_Developer”,”_”) = 5

Default Dcount is 1

Dcount(“Datastage”,”_”) = 1

DownCase

Description:

Change all uppercase letters in a string to lowercase.

Syntax:

DownCase(string)

Example:

String= “DATASTAGE”

DownCase(string)= “datastage”

DQuote

Description:

Enclose a string in double quotation marks,

Syntax:

DQuote(%string%)

Example:

DQuote(Data stage) = “Data stage”

Field

Description:

Return 1 or more delimited substrings

Syntax:

Field (string, delimiter, instance [ ,number] )

Example:

Field (“ETL_Data_stage_8″,”_”, 2)

Returns : Data

Index

Description:

Find starting character position of substring

Syntax:

Index(%string%,%substring%,[%n%])

Example:

string = “these are the results”

Index (string,”re”,1) = 8

Index (string,”re”,2) = 15

If srting is a null value, 0 is returned

If subsrting is a null value, 0 is returned

If instance is a null value, it generates a run-time error.

Left

Description:

Leftmost n characters of string

Syntax:

Left(%string%,%length%)

Example:

string = “I Love you”

Left[1,6] = “I Love”

Len

Description:

Length of string in characters

Syntax:

Len(%string%)

Example:

Len(“I Love you”) = 10

Num

Description:

Return 1 if string can be converted to a number

Syntax:

Num(%string%)

Example:

NUM(“143.23″) = 1 true

NUM(“abc123″)= 0 false

NUM(“”)= 1 true (Empty string is numeric)

NUM(“ “)= 0 false (Space is not numeric)

PadString

Description:

Return the string padded with the optional pad character and optional length

Syntax:

PadString(%string%,%char%,%length%)

Example:

PadStr(“Data”,”x”,3) = “Dataxxx”

Right

Description:

Rightmost n characters of string

Syntax:

Right(%string%,%length%)

Example:

string = “I Love you”

Right[1,3] = “you”

Space

Description:

Return a string of N space characters.

Syntax:

Space(%length%)

Example:

Space(3) = ” ”

Squote

Description:

Enclose a string in single quotation marks

Syntax:

SQuote(%string%)

Example:

SQuote(Data stage) = „Data stage‟

Str

Description:

Repeat a string.

Syntax:

Str(%string%,%repeats%)

Example:

Arg: 1234567

Str(“0″,10-Len(Arg)):Arg =0001234567

Arg: Str(“0″,10-Len(Arg)) =1234567000

Trim

Description:

Remove all leading and trailing spaces and tabs plus reduce internal occurrences to one

Syntax:

Trim(%string%,[%stripchar%],[%option%])

Possible options for the Trim function are:

L Removes leading occurrences of character.

T Removes trailing occurrences of character.

B Removes leading and trailing occurrences of character.

R Removes leading and trailing occurrences of character, and reduces multiple occurrences to a single occurrence.

A Removes all occurrences of character.

F Removes leading spaces and tabs.

E Removes trailing spaces and tabs.

D Removes leading and trailing spaces and tabs, and reduces multiple spaces and tabs to single ones.

Example:

Trim(” String with whitespace “)

Returns “String with whitespace”

Trim(“..Remove..redundant..dots….”, “.”)

Returns “Remove.redundant.dots”

Trim(“Remove..all..dots….”, “.”, “A”)

Returns “Removealldots”

Trim(“Remove..trailing..dots….”, “.”, “T”)

Returns “Remove..trailing..dots”

TrimB

Description:

Remove all trailing spaces and tabs.

Syntax:

TrimB(%string%)

Example:

TrimB(” String with whitespace ”)

Returns: ” String with whitespace”

TrimF

Description:

Remove all leading spaces and tabs.

Syntax:

TrimF(%string%)

Example:

TrimF(” String with whitespace “)

Returns: ”String with whitespace ”

Trim Leading Trailing

Description:

Returns a string with leading and trailing whitespace removed.

Syntax:

Trim Leading Trailing(%string%)

Example:

Trim Leading Trailing(“ Trim leading Trailing “)

returns : “Trim leading Trailing“ Upcase

Description:

Change all lowercase letters in a string to uppercase.

Syntax:

UpCase(string)

Example:

String= ” datastage ”

UpCase(string)= “DATASTAGE”

FIELD function

Syntax

FIELD (string, delimiter, occurrence [ ,num.substr] )

Description

Use the FIELD function to return one or more substrings located between specified delimiters in string.

delimiter evaluates to any character, including field mark, value mark, and subvalue marks. It delimits the start and end of the substring. If delimiter evaluates to more than one character, only the first character is used. Delimiters are not returned with the substring.

occurrence specifies which occurrence of the delimiter is to be used as a terminator. If occurrence is less than 1, 1 is assumed.

num.substr specifies the number of delimited substrings to return. If the value of num.substr is an empty string or less than 1, 1 is assumed. When more than one substring is returned, delimiters are returned along with the successive substrings.

If either delimiter or occurrence is not in the string, an empty string is returned, unless occurrence specifies 1. If occurrence is 1 and delimiter is not found, the entire string is returned. If delimiter is an empty string, the entire string is returned.

If string evaluates to the null value, null is returned. If string contains CHAR(128) (that is, @NULL.STR), it is treated like any other character in a string. If delimiter, occurrence, or num.substr evaluate to the null value, the FIELD function fails and the program terminates with a run-time error message.

The FIELD function works identically to the GROUP function.

Examples

D=FIELD("###DHHH#KK","#",4)

PRINT "D= ",D

The variable D is set to DHHH because the data between the third and fourth occurrence of the delimiter # is DHHH.

REC="ACADABA"

E=FIELD(REC,"A",2)

PRINT "E= ",E

The variable E is set to "C".

VAR="?"

Z=FIELD("A.1234$$$$&&",VAR,3)

PRINT "Z= ",Z

Z is set to an empty string since "?" does not appear in the string.

Q=FIELD("+1+2+3ABAC","+",2,2)

PRINT "Q= ",Q

Q is set to "1+2" since two successive fields were specified to be returned after the second occurrence of "+".

This is the program output:

D= DHHH

E= C

Z=

Q= 1+2