String functions | IBM Cloud Pak for Data as a Service

String functions

Use the string functions to manipulate strings.

The following functions are in the String category of the expression editor. Square brackets indicate that an argument is optional. The examples show the function as it appears in a Derivation field in the Transformer stage.

AlNum

Checks whether the given string contains only alphanumeric characters.

Input: string (string)
Output: 0 or 1 (int8)
Examples. If mylink.mystring1 contains the string "OED_75_9*E", then the following function would return the value 0 (false).
```
AlNum(mylink.mystring1)
```
If mylink.mystring2 contains the string "12redroses", then the following function would return the value 1 (true).
```
AlNum(mylink.mystring2)
```

Alpha

Checks whether the given string contains only alphabetic characters.

Input: string (string)
Output: 0 or 1 (int8)
Examples. If mylink.mystring1 contains the string "12redroses", then the following function would return the value 0 (false).
```
Alpha(mylink.mystring1)
```
If mylink.mystring2 contains the string "twelveredroses", then the following function would return the value 1 (true).
```
Alpha(mylink.mystring2)
```

Change

Replaces the given substring in an expression with a replacement string. If the substring argument is an empty string, the value of the expression argument is returned. If the value of the replacement argument is an empty string, all occurrences of the substring starting from the position indicated by the value of the begin argument are removed. If the value of the occurrence argument is less than or equal to 0, all occurrences starting from the position indicated by the value of the begin argument are replaced. Otherwise, the number of occurrences that are replaced is indicated by the value of the occurrence argument, starting from the position indicated by the value of the begin argument. If the value of the begin argument is less than or equal to 1, the replacement starts from the first occurrence. Otherwise, the replacement starts from the position indicated by the value of thebegin argument.

Input: expression (string), substring (string), replacement (string), [occurrence (int32), [begin (int32)]]
Output: result (string)
Examples. If mylink.mystring contains the expression "aaabbbcccbbb", then the following function returns the string "aaaZZZcccZZZ".
```
Change(mylink.mystring,"bbb","ZZZ")
```
If mylink.mystring contains the expression "ABC" and the substring is empty, then the following function returns the string "ABC".
```
Change(mylink.mystring,"","ZZZ")
```
If mylink.mystring contains the expression “aaabbbcccbbb” and the replacement is empty, then the following function returns the string "aaaccc".
```
Change(mylink.mystring, “bbb”, “”)
```

CompactWhiteSpace

Returns the string after reducing all consecutive white space to a single space.

Input: string (string)
Output: result (string)
Examples. If mylink.mystring contains the string "too many spaces", then the following function returns the string "too many spaces":
```
CompactWhiteSpace(mylink.mystring)
```

Compare

Compares two strings for sorting. The comparison can be left-aligned (the default) or right-aligned. A right-aligned comparison compares numeric substrings within the specified strings as numbers. The numeric strings must occur at the same character position in each string. For example, a right-aligned comparison of the strings AB100 and AB99 indicates that AB100 is greater than AB99 since 100 is greater than 99. A left-aligned comparison of the strings AC99 and AB100 indicates that AC99 is greater since C is greater than B.

Input: string1 (string), string2 (string), [justification (L or R)]
Output: result (int8), can be -1 for string1 is less than string2, 0 for both strings are the same, 1 for string1 is greater than string2.
Examples. If mylink.mystring1 contains the string "AB99" and mylink.mystring2 contains the string "AB100", then the following function returns the result 1.
```
Compare(mylink.mystring1,mylink.mystring2,L)
```
If mylink.mystring1 contains the string "AB99" and mylink.mystring2 contains the string "AB100", then the following function returns the result -1.
```
Compare(mylink.mystring1,mylink.mystring2,R)
```

CompareNoCase

Compares two strings for sorting, ignoring their case.

Input: string1 (string), string2 (string)
Output: result (int8), can be -1 for string1 is less than string2, 0 for both strings are the same, 1 for string1 is greater than string2.
Examples. If mylink.mystring1 contains the string "Chocolate Cake" and mylink.mystring2 contains the string "chocolate cake", then the following function returns the result 0.
```
CompareNoCase(mylink.mystring1,mylink.mystring2)
```

CompareNum

Compares the first n characters of two strings.

Input: string1 (string), string2 (string), length (int16)
Output: result (int8), can be -1 for string1 is less than string2, 0 for both strings are the same, 1 for string1 is greater than string2.
Examples. If mylink.mystring1 contains the string "Chocolate" and mylink.mystring2 contains the string "Choccy Treat", then the following function returns the result 0.
```
CompareNum(mylink.mystring1,mylink.mystring2,4)
```

CompareNumNoCase

Compares the first n characters of two strings, ignoring their case.

Input: string1 (string), string2 (string), length (int16)
Output: result (int8), can be -1 for string1 is less than string2, 0 for both strings are the same, 1 for string1 is greater than string2.
Examples. 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)
```

Conversion

Converts a string to a specified internal or external storage format. The string expression evaluates the string to be converted.

Input: string (string), conv_code (string), conv_mode (string)

The following table shows the values that you can specify for the conv_code and conv_mode. If you specify I for conv_mode, the ICONV() function is used for the conversion. If you specify O for conv_mode, the OCONV() function is used for the conversion.

Table 1. Values for the conv_code and conv_mode.
conv_code	conv_mode	Description
MX	I	Converts the input string from hexadecimal to decimal.
MB	I	Converts the input string from binary to decimal.
MX0C	I	Converts the input string from hexadecimal to ASCII character string.
MB0C	I	Converts the input string from binary to ASCII character string.
MX	O	Converts the input string from decimal to hexadecimal.
MB	O	Converts the input string from decimal to binary.
MX0C	O	Converts the input string from ASCII character string to hexadecimal.
MB0C	O	Converts the input string from ASCII character string to binary.

Output: result (string)
Examples. If mylink.mystring contains the string "1111", then the following function returns the value 15.
```
Conversion(mylink.mystring,"MB", "I")
```
If mylink.mystring contains the string "CDE", then the following function returns the value 434445.
```
Conversion(mylink.mystring,"MX0C", "O")
```

Convert

Converts the characters in the string that is designated in the given expression. Converts the characters that are specified in one list to the characters specified in another list.

Input: fromlist (string), tolist (string), expression (string)
Output: result (string)
Examples. If mylink.mystring1 contains the string "NOW IS THE TIME", then the following function returns the string "NOW YS XHE XYME".
```
Convert("TI","XY",mylink.mystring1)
```

ConvertDatum

Converts a given date string to the "YYYYMMDD" format. The given date string must be in the "DDMMYY" or "DDMMYYYY" date format. The year in the date cannot be 3000 or more. Format represents the pattern of the year specified in the given date string.

Input: date (string), format (string)
Output: result (string)
Examples. If mylink.date contains the date string "01082022", then the following function returns the date string "20220801":
```
ConvertDatum(mylink.date, "YYYY")
```

Count

Counts the number of times a substring occurs in a string.

Input: string (string), substring (string)
Output: result (int32)
Examples. If mylink.mystring1 contains the string "chocolate drops, chocolate ice cream, chocolate bars", then the following function returns 3.
```
Count(mylink.mystring1,"choc")
```

Dcount

Counts the number of delimited fields in a string.

Input: string (string), delimiter (string)
Output: result (int32)
Examples. If mylink.mystring1 contains the string "chocolate drops, chocolate ice cream, chocolate bars", then the following function returns 3.
```
Dcount(mylink.mystring1,",")
```

DownCase

Changes all uppercase letters in a string to lowercase letters.

Input: string (string)
Output: result (string)
Examples. If mylink.mystring1 contains the string "CaMel cAsE", then the following function returns the string "camel case".
```
DownCase(mylink.mystring1)
```

DQuote

Encloses a string in double quotation marks.

Input: string (string)
Output: result (string)
Examples. If mylink.mystring1 contains the string "needs quotes", then the following function returns the string ""needs quotes"".
```
DQuote(mylink.mystring1)
```

DecryptString

Decrypts (AES-256) input cipher text with 32-byte key.

Input: cipher-text (string), key (string)
Output: result (string)
Examples. If Link_1.Phone contains an encrypted string, this function will output the decrypted string "+1 123-456-7890" .
```
DecryptString(Link_1.Phone, "12345678912345678912345678912345")
```

EncryptString

Encrypts (AES-256) input string with 32-byte key.

Input: plain-text (string), key (string)
Output: result (string)
Examples. If Link_1.Phone contains the string "+1 123-456-7890", this function will output an encrypted string.
```
EncryptString(Link_1.Phone, "12345678912345678912345678912345")
```

Ereplace

Replaces a substring in an expression with replacement string. If the substring argument is an empty string, the value of the replacement argument is prefixed with the value of the expression argument. If the replacement argument is an empty string, all occurrences of the substring starting from the position indicated by the value of the begin argument are removed. If the value of the occurrence argument is less than or equal to 0, all occurrences starting from the value of the begin argument are replaced. Otherwise, the number of occurrences that are replaced is indicated by the value of the occurrence argument, starting from the position indicate by the value of the begin argument. If value of the begin argument is less than or equal to 1, the replacement starts from the first occurrence. Otherwise, the replacement starts from the position indicated by the value of the begin argument.

Input: expression (string), substring (string), replacement (string), [occurrence (int32), [begin (int32)]]
Output: result (string)
Examples. If mylink.mystring contains the expression "ABC" and the substring is empty, the following function returns the value "ZZZABC".
```
Ereplace(mylink.mystring,"","ZZZ")
```
If mylink.mystring contains the expression "aaabbbcccbbb" and the replacement is empty, the following function returns the value "aaaccc".
```
Ereplace(mylink.mystring, "bbb", "")
```

Field

Returns one or more substrings that are located between specified delimiters in a string. The argument occurrence specifies which occurrence of the delimiter is to be used as a terminator. The argument number optionally specifies how many substrings to return.

Input: string (string), delimiter (string), occurrence (int32), [number (int32)]
Output: result (string)
Examples. 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)
```

Fieldstore

Modifies character strings by inserting, deleting, or replacing fields separated by specified delimiters.

Input: string (string), delimiter (string), start (int32), field (int32), new string (string)
Output: result (string)
Examples. If mylink.mystring1 contains the string "racecar|level", then the following function returns the string " racecar|noon".
```
Fieldstore(mylink.mystring1,"|",2,2,"noon")
```

FindReplace

Finds all occurrences of a substring in the source string and replaces them with the replacement string.

Input: source string (string), substring (string), replacement string (string)
Output: result (string)
Examples. If mylink.mystring1 contains the string "A-B-C" and the find substring is "-" and the replacement string is ", " then the following function returns the string "A, B, C".
```
FindReplace(mylink.mystring1, "-", ", ")
```

Fmt

Format data for output. The format string argument is an expression specifying how to format string.

Input: string (string), format string (string)
Output: result (string)
Examples. If mylink.mystring contains the string 1234567890, then the following function returns the result value 1234-5678-9000-0000:
```
Fmt(1234567890, '%%%%-%%%%-%%%%-%%%%')
Fmt(mylink.mystring, '%%%%-%%%%-%%%%-%%%%')
```

Fold

Divides a string into a number of substrings separated by field marks.

Input: string (string), number (int32)
Output: result (string)
Examples. If mylink.mystring1 contains the string "THIS IS A FOLDED STRING.", then the following function returns the string "THISFISAFFOLDEFDFSTRINFG."
```
Fold(mylink.mystring1, 5)
```

Index

Finds the starting character position of a substring. The argument occurrence specifies which occurrence of the substring is to be located.

Input: string (string), substring (string), occurrence (int32)
Output: result (int32)
Examples. If mylink.mystring1 contains the string "chocolate drops, chocolate ice cream, chocolate bars, chocolate dippers", then the following function returns the value 18.
```
Index(mylink.mystring1,"chocolate",2)
```

Left

Returns the leftmost n characters of a string.

Input: string (string) number (int32)
Output: result (string)
Examples. If mylink.mystring1 contains the string "chocolate drops, chocolate ice cream, chocolate bars, chocolate dippers", then the following function returns the string "chocolate".
```
Left(mylink.mystring1,9)
```

Len

Returns the length of a string in characters.

Input: string (string)
Output: result (int32)
Examples. If mylink.mystring1 contains the string "chocolate", then the following function returns the value 9.
```
Len(mylink.mystring1)
```

MatchField

Checks a string against a match pattern.

Input: string (string), pattern (string), field (int32)
Output: result (int8)
Examples. pattern must contain specifiers to cover all characters contained in a string. If mylink.mystring1 contains the string "XYZ123AB", then the following function returns an empty string because not all parts of string are specified in the pattern.
```
MatchField(mylink.mystring1, "3X3N", 1)
```

Num

Returns 1 if the string can be converted to a number, or returns 0 otherwise.

Input: string (string)
Output: result (int32)
Examples. If mylink.mystring1 contains the string "22", then the following function returns the value 1.
```
Num(mylink.mystring1)
```
If mylink.mystring1 contains the string "twenty two", then the following function returns the value 0.
```
Num(mylink.mystring1)
```

NextValidDate

Gets the next valid date. The given string must be in the "yyyy-mm-dd" date format.

Input: date (string)
Output: result (string)
Examples. If mylink.date contains the string "2021-12-32", then the following function returns the date string "2022-01-01":
```
NextValidDate(mylink.date)
```

OffsetOfSubstring

Returns the starting position of the first occurrence of substring in the string starting from the %search_start% which is 0-based. If the third argument is smaller than 0 or greater than the total length of the first argument - 1, -1 is returned. Otherwise, if the substring is not found, a value equal to the third argument minus 1 is returned. The starting position is 0-based as well.

Input: string (string), substring_string (substring string), search_start (search start)
Output: number (number)
Examples. If mylink.mystring1 contains the string "chocolate drops, chocolate ice cream, chocolate bars, chocolate dippers",then the following function returns the value 17.
```
OffsetOfSubstring(mylink.mystring1, "chocolate", 2)
```

OffsetOfSubstring1

Returns the starting position of the first occurrence of substring in the string starting from the %search_start% which is 1-based. If the third argument is smaller than 1 or greater than the total length of the first argument, -1 is returned. Otherwise, if the substring is not found, a value equal to the third argument is returned. The starting position is 1-based as well.

Input: string (string), substring_string (substring string), search_start (search start)
Output: number (number)
Examples. If mylink.mystring1 contains the string "chocolate drops, chocolate ice cream, chocolate bars, chocolate dippers", then the following function returns the value 18.
```
OffsetOfSubstring1(mylink.mystring1, "chocolate", 2)
```

PadString

Returns the string padded with the specified number of pad characters.

Input: string (string) padstring (string) padlength (int32)
Output: result (string)
Examples. If mylink.mystring1 contains the string "AB175", then the following function returns the string "AB17500000".
```
PadString(mylink.mystring1,"0",5)
```

Reverse

Reverses a string.

Input: string (string)
Output: result (string)
Examples. If mylink.mystring1 contains the string "Hello world", then the following function returns the string "dlrow olleH".
```
Reverse(mylink.mystring1)
```

Right

Returns the rightmost n characters of a string.

Input: string (string) number (int32)
Output: result (string)
Examples. If mylink.mystring1 contains the string "chocolate drops, chocolate ice cream, chocolate bars, chocolate dippers", then the following function returns the string "dippers".
```
Right(mylink.mystring1,7)
```

RmUnprint

Removes an unprintable string.

Input: string (string)
Output: result (string)
Examples. If the mylink.mystring contains "��Test��", the following function returns the result value "Test":
```
RmUnprint("����Test����")
RmUnprint(mylink.mystring)
```

Soundex

Returns a code that identifies a set of words that are (roughly) phonetically alike based on the standard, open algorithm for SOUNDEX evaluation.

Input: string (string)
Output: result (string)
Examples. If mylink.mystring1 contains the string "Griffin", then the following function returns the code "G615".
```
Soundex(mylink.mystring1)
```
If mylink.mystring1 contains the string "Griphin" then the following function also returns the code "G615".
```
Soundex(mylink.mystring1)
```

Space

Returns a string of n space characters.

Input: length (int32)
Output: result (string)
Examples. If mylink.mylength contains the number 100, then the following function returns a string that contains 100 space characters.
```
Space(mylink.mylength)
```

SQuote

Encloses a string in single quotation marks.

Input: string (string)
Output: result (string)
Examples. If mylink.mystring1 contains the string needs quotes, then the following function returns the string 'needs quotes'.
```
SQuote(mylink.mystring1)
```

Str

Repeats a string the specified number of times.

Input: string (string) repeats (int32)
Output: result (string)
Examples. If mylink.mystring1 contains the string needs "choc", then the following function returns the string "chocchocchocchocchoc".
```
Str(mylink.mystring1,5)
```

StrCmp

Compares two strings by lexicographical order. Returns "1" if string1 is greater than string2. Returns "-1" if string1 is less than string2. Returns zero if string1 and string2 are equal. This function is present for compatibility but use is not encouraged.

Input: string1 (string), string2 (string)
Output: result (int32)
Examples. If mylink.mystring1 is "world", and mylink.mystring2 is "hello", then the following function returns 1:
```
StrCmp("world", "hello")
StrCmp(mylink.mystring1, mylink.mystring2)
```

StripWhiteSpace

Returns the string after removing all white space characters from it.

Input: string (string)
Output: result (string)
Examples. If mylink.mystring contains the string "too many spaces", then the following function returns the string "toomanyspaces":
```
StripWhiteSpace(mylink.mystring)
```

SubstituteString

Replaces one or all occurrences of a substring within a string.

Input: string_to_replace (string), substring (string), substring_replacement (string), substring_num (int8), direction_num (int8), mode_num (int8)
Output: result (string)
Examples: If the column mylink.mystring contains the string "123451234512345", then the following function returns the value "aa2345aa2345aa2345":
```
SubstituteString(mylink.mystring, "1", "aa", 0, 1, 1)
```

Trim

Removes all leading and trailing spaces and tabs. The function also reduces the internal occurrences of spaces or tabs to one. The argument stripchar optionally specifies a character other than a space or a tab. The argument options optionally specifies the type of trim operation to be performed and contains one or more of the following values:

A Remove all occurrences of stripchar.

B Remove both leading and trailing occurrences of stripchar.

D Remove leading, trailing, and redundant white-space characters.

E Remove trailing white-space characters.

F Remove leading white-space characters.

L Remove all leading occurrences of stripchar.

R Remove leading, trailing, and redundant occurrences of stripchar.

T Remove all trailing occurrences of stripchar.

Input: string (string) [stripchar (string)] [options (string)]
Output: result (string)
Examples. 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,".")
```
If mylink.mystring contains the string "Remove..all..dots....", then the following function returns the string "Removealldots":
```
Trim(mylink.mystring,".","A")
```
If mylink.mystring contains the string "Remove..trailing..dots....", then the following function returns the string "Remove..trailing..dots":
```
Trim(mylink.mystring,".","T")
```

The Trim function uses both variable and fixed length fields. In DataStage®, fixed length fields are padded either with the NULL character by default or the character given by APT_STRING_PADCHAR. When you trim trailing spaces from a fixed length field in the Transformer stage, the operation fails unless the field data is equal to the fixed length of the field. In fixed length fields the last character of the field is always NULL or APT_STRING_PADCHAR.

TrimB

Removes all trailing spaces and tabs from a string.

Input: string (string)
Output: result (string)
Examples. If mylink.mystring contains the string "too many trailing spaces ", then the following function returns the string "too many trailing spaces":
```
TrimB(mylink.mystring)
```

TrimF

Removes all leading spaces and tabs from a string.

Input: string (string)
Output: result (string)
Examples. If mylink.mystring contains the string " too many leading spaces", then the following function returns the string "too many leading spaces":
```
TrimF(mylink.mystring)
```

TrimLeadingTrailing

Removes all leading and trailing spaces and tabs from a string.

Input: string (string)
Output: result (string)
Examples. If mylink.mystring contains the string " too many spaces ", then the following function returns the string "too many spaces":
```
TrimLeadingTrailing(mylink.mystring)
```

UpCase

Changes all lowercase letters in a string to uppercase.

Input: string (string)
Output: result (string)
Examples. If mylink.mystring1 contains the string "CaMel cAsE", then the following function returns the string "CAMEL CASE".
```
UpCase(mylink.mystring1)
```

UrlDecode

Decodes a URL encoded string to its original representation. The URL encoded string was previously encoded by using either RCF 3986 or RCF 1738.

Input: string (string)
Output: result (string)
Examples. If mylink.urlFragment contains the string "Address%3DHampshire,United%20Kingdom", then the following function returns the string "Address=Hampshire,United Kingdom":
```
UrlDecode(mylink.urlFragment)
```

UrlEncode

Converts a string to a URL encoded format.

Input: string (string)
Output: result (string)
Examples. If mylink.string contains the string "Name=John Smith/Address=Hampshire\~United Kingdom/Telephone=+441962808000/", then the following function returns the encoded string "Name%3DJohn%20Smith%2FAddress%3DHampshire~United%20Kingdom%2FTelephone%3D%2B441962808000%2F":
```
UrlEncode(myLink.string)
```