1.1 LENGTH:
The LENGTH function returns the number of characters in a string, including trailing blanks. It is available in the Designer and the Workflow Manager.
LENGTH (string)
1.2 LPAD:
The LPAD function adds a set of blanks or characters to the beginning of a string, to set a string to a specified length. It is available in the Designer and the Workflow Manager.
LPAD (first_string, length [, second_string])
Example: The following expression standardizes numbers to five digits by padding them with leading zeros.
1.3 LTRIM:
The LTRIM function removes blanks or characters from the beginning of a string. It is available in the Designer and the Workflow Manager.
LTRIM (string [, trim_set])
LTRIM (string) removes the leading spaces or blanks from the string. When LTRIM function is used with a trim set, which is optional, it removes the characters in the trim set from the string.
Example: The following expression removes the leading zeroes in the port ITEM_CODE.
LTRIM (ITEM_CODE,'0')
| ITEM_CODE | RETURN VALUE |
| 006 | 6 |
| 0803 | 803 |
* The LTRIM function can be nested when needed to remove multiple characters.
1.4 RPAD:
The RPAD function converts a string to a specified length by adding blanks or characters to the end of the string. It is available in the Designer and the Workflow Manager.
RPAD( first_string, length [, second_string ] )
Example: The following expression returns the string with a length of 5 characters, appending the string ':' to the end of each word:
RPAD (WORD, 5, ':’)
1.5 RTRIM:
The RTRIM function removes blanks or characters from the end of a string. It is available in the Designer and the Workflow Manager.
RTRIM (string [, trim_set])
The RTRIM function can be combined with the LENGTH function if the trailing blanks are to be ignored. It can also be nested when needed to remove multiple characters.
RTRIM (string) removes the trailing spaces or blanks from the string. When RTRIM function is used with a trimset, which is optional, it removes the characters in the trimset from the string.
For example,
RTRIM (ITEM_CODE,'10')
The above expression removes the characters 10 in the port ITEM_CODE.
| ITEM_CODE | RETURN VALUE |
| 0610 | 06 |
| 380 | 38 |
In the second example the function removes the trailing zero since the RTRIM compares the first character in the trimset with the last character of the string, since it does not match it takes the second character in the trimset and compares with last character of the string. Since it matches it removes it.
1.6 SUBSTR:
The SUBSTR function returns a portion of a string. It is available in the Designer and the Workflow Manager.
SUBSTR( string, start [, length ] )
The SUBSTR may not give the desired result if the string on which it is used is not trimmed. Though it is always a good practice to trim the strings before using them in any expression, it becomes extremely important to trim them if they are used in a SUBSTR function.
For example, if there is a function
SUBSTR (NAME, 2,2)
It will not return the 2,3 characters of the NAME if the port has leading spaces. In this case LTRIM becomes essential.
SUBSTR(LTRIM(NAME),2,2)
The SUBSTR function can also be used to get the last few characters as described below.
SUBSTR(NAME,-3,3)
This function will return the last three characters of the string. But it may not return the required last three characters if the port has trailing blanks, hence RTRIM is essential.
SUBSTR(RTRIM(NAME),-3,3)
Hence it is always better to trim the strings before using them in a SUBSTR function.
SUBSTR(LTRIM(RTRIM(NAME)),3,2)
The above expression will get the 3,4 character of the port NAME irrespective of whether the port has leading or trailing blanks or not.
No comments:
Post a Comment