String functions in Datastage

Reading Time: 4 minutes
AlNum(mylink.mystring1)Checks whether the given string contains only alphanumeric characters.
If mylink.mystring1 contains the string “OED_75_9*E”, then the following function would return the value 0 (false)
Alpha(mylink.mystring1)Checks whether the given string contains only alphabetic characters
If mylink.mystring1 contains the string “12 red roses”, then the following function would return the value 0 (false).
CompactWhiteSpace(mylink.mystring)Return the string after reducing all consecutive white space to a single space.
 If mylink.mystring contains the string “too   many    spaces”, then the following function returns the string “too many spaces”:
Compare(mylink.mystring1,mylink.mystring2,L)Compare two strings
CompareNoCase(mylink.mystring1,mylink.mystring2)Compares two strings for sorting, ignoring their case
CompareNum(mylink.mystring1,mylink.mystring2,4)If mylink.mystring1 contains the string “Chocolate” and mylink.mystring2 contains the string “Choccy Treat”, then the following function returns the result 0
CompareNumNoCase(mylink.mystring1,mylink.mystring2,4)If mylink.mystring1 contains the string “chocolate” and mylink.mystring2 contains the string “Choccy Treat”, then the following function returns the result 0
Convert(“TI”,”XY”,mylink.mystring1)If mylink.mystring1 contains the string “NOW IS THE TIME”, then the following function returns the string “NOW YS XHE XYME”.
Count(mylink.mystring1,”choc”)Counts the number of times a substring occurs in a string
If mylink.mystring1 contains the string “chocolate drops, chocolate ice cream, chocolate bars”, then the following function returns 3
Dcount(mylink.mystring1,”,”)Counts the number of delimited fields in a string
If mylink.mystring1 contains the string “chocolate drops, chocolate ice cream, chocolate bars”, then the following function returns 3
DownCase(mylink.mystring1)Changes all uppercase letters in a string to lowercase
If mylink.mystring1 contains the string “CaMel cAsE”, then the following function returns the string “camel case”.
UpCase(mylink.mystring1)If mylink.mystring1 contains the string “CaMel cAsE”, then the following function returns the string “CAMEL CASE”.
DQuote(mylink.mystring1)Encloses a string in double quotation marks
If mylink.mystring1 contains the string needs quotes, then the following function returns the string “needs quotes”.
SQuote(mylink.mystring1)Encloses a string in single quotation marks.
If mylink.mystring1 contains the string needs quotes, then the following function returns the string ‘needs quotes’.
Field(mylink.mystring1,”,”,2)If mylink.mystring1 contains the string “chocolate drops, chocolate ice cream, chocolate bars, chocolate dippers”, then the following function returns the string ” chocolate ice cream”.
Field(mylink.mystring1,”,”,2)If mylink.mystring1 contains the string “chocolate drops, chocolate ice cream, chocolate bars, chocolate dippers”, then the following function returns the string ” chocolate ice cream, chocolate bars”.
Field(mylink.mystring1,”,”,2,2)
Index(mylink.mystring1,”chocolate”,2)If mylink.mystring1 contains the string “chocolate drops, chocolate ice cream, chocolate bars, chocolate dippers”, then the following function returns the value 18
Left(mylink.mystring1,9)If mylink.mystring1 contains the string “chocolate drops, chocolate ice cream, chocolate bars, chocolate dippers”, then the following function returns the string “chocolate”.
Right(mylink.mystring1,7)If mylink.mystring1 contains the string “chocolate drops, chocolate ice cream, chocolate bars, chocolate dippers”, then the following function returns the string “dippers”
Num(mylink.mystring1)Returns 1 if string can be converted to a number, or 0 otherwise
If mylink.mystring1 contains the string “22”, then the following function returns the value 1
If mylink.mystring1 contains the string “twenty two”, then the following function returns the value 0
PadString(mylink.mystring1,”0″,5)Return the string padded with the specified number of pad characters
IIf mylink.mystring1 contains the string “AB175”, then the following function returns the string “AB17500000”.
Space(mylink.mylength)Returns a string of n space characters.
 If mylink.mylength contains the number 100, then the following function returns a string that contains 100 space characters
Str(mylink.mystring1,5)Repeats a string the specified number of times
If mylink.mystring1 contains the string needs “choc”, then the following function returns the string “chocchocchocchocchoc”.
StripWhiteSpace(mylink.mystring)Returns the string after removing all whitespace characters from it
If mylink.mystring contains the string “too   many    spaces”, then the following function returns the string “toomanyspaces”:
Trim(mylink.mystring) If mylink.mystring contains the string ” String with whitespace “, then the following function returns the string “String with whitespace”:
Trim(mylink.mystring,”.”)If mylink.mystring contains the string “..Remove..redundant..dots….”, then the following function returns the string “Remove.redundant.dots”:
Trim(mylink.mystring,”.”,”A”) 
TrimB(mylink.mystring)Removes all trailing spaces and tabs from a string.
If mylink.mystring contains the string “too many trailing spaces    “, then the following function returns the string “too many trailing spaces”:
TrimF(mylink.mystring)Removes all leading spaces and tabs from a string
If mylink.mystring contains the string ”    too many leading spaces”, then the following function returns the string “too many leading spaces”:
Removes all leading and trailing spaces and tabs from a string
 If mylink.mystring contains the string ” too many spaces “, then the following function returns the string “too many spaces”:

Leave a Reply