String functions

Following are brief descriptions of the functions that apply to string values. For more detailed information about a specific function (such as usage and syntax), click the function name.

Function What it does

convert

Converts a string to another data type or converts another data type to a string.

decode Converts an encoded string expression back to the original string.
encode Converts a string expression to a meaningless text string.

hex

Translates a string literal containing hexadecimal character codes into an ASCII string.

in_set

Returns the boolean value true if one string is composed entirely of characters from another string.

is_number

Returns the boolean value true if a string expression evaluates to a float or integer value.

is_time

Returns the boolean value true if a string expression evaluates to a valid time value.

len

Returns the length of a string.

lower

upper

Changes a string's characters to lowercase or uppercase.

ltrim

rtrim

trim

Deletes blanks or specified characters from the beginning (ltrim), end (rtrim), or both ends (trim) of a string.

max

Returns the largest value from a list of expressions of any supported data type.

min

Returns the smallest value from a list of expressions of any supported data type.

numbertostring

Converts a float or integer expression to a string expression, using a specified mask.

repeat

Creates a string by repeating another string a specified number of times.

strins

Inserts one string into another string at a specified location.

strloc

Returns the starting position of a substring within a string. If the substring is not found, the function returns zero.

stringtotime

Converts a string expression to a time expression, using a specified mask.

substitute

Within a string, replaces one substring with another.

substr

Returns a substring given a specified starting position and length.

timetostring

Converts a time expression to a string expression, using a specified mask.

translate

Replaces characters in a string.

xmlencode Converts an XML field to a string expression.
xmldecode Converts a string expression to XML.

 

hex

This function translates a string literal of hexadecimal character codes into an ASCII string. The hex function can express any character, although you generally use it for characters that do not have ASCII representations, such as tabs.

Every two hex digits represent one character in the resulting string. An odd number of hex characters in the string literal results in an error. Use the following syntax:

hex (hex_codes)

where hex_codes is a string literal containing the hex code for a character or a series of characters. The following example returns the string "abc" because 61, 62, and 63 are the hexadecimal codes for those characters.

hex ("616263")

translate

This function creates a new string by replacing the characters in one string with the characters you specify. The translate function is useful for encoding characters.

Use the following syntax:

translate (source_string, search_string, replace_string)

where:

source_string

is the string containing the characters to be replaced.

search_string

is the string of characters within source_string to replace. Must be the same length as replace_string.

replace_string

is the string of replacement characters. Must be the same length as search_string.

The search-and-replace occurs on a per-character basis. If the search string is "abc" and the replace string is "def", the function translates "a" to "d", "b" to "e", and "c" to "f" in the newly created string.

For example, with the following values,

source_string contains "*123-#44-!999"

search_string contains "#*!-"

replace_string contains "XYZ&"

the function outputs the following new string:

Y123&X44&Z999

Note that the contents of the source string, the search string, and the replace string do not change.

TIP To search for and replace an entire substring rather than individual characters, use the substitute function.

The following example uses the translate function to convert a string from European number format to American number format.

translate (str, ".,", ",.")

lower, upper

These functions convert all the characters in a string to lower- or upper- case, respectively. Use the following syntax:

lower (string_exp)

upper (string_exp)

where string_exp is the string expression to convert.

 

in_set

This function compares two strings, returning a boolean value of true if every character in the first string is contained somewhere in the second string. If the first string contains at least one character that is not also contained in the second string, the function returns a value of false. The characters need not appear in the same order in both strings; also, the second string can contain additional characters.

Use the following syntax:

in_set (subset_string, superset_string)

where:

subset_string

is the string expression containing only the subset characters.

superset_string

is the string expression to which subset_string is compared.

In the following example,

in_set (name, "abcdefghijklmnopqrstuvwxyz")

the function returns true if name = "jim" but false if name = "Fido" or name = "a.b".

len

This function computes the actual (not the declared) length of a string expression. It gives the total number of characters, including spaces, in the expression. Use the following syntax:

len (string_expr)

where string_expr is a string expression whose length is to be calculated.

In the following example, the length returned is 15. Note that the enclosing quotes are not counted as characters.

len ("Hamilton Burger")

repeat

This function creates a string by repeating a given string a specified number of times. Use the following syntax:

repeat (string_expr, repeat_num)

where:

string_expr

is the string expression to be repeated.

repeat_num

is the number of times to repeat string_exp.

The following example outputs the string abcdabcdabcdabcdabcd.

repeat ("abcd", 5)

strloc

A substring is a part of a source string; it can be less than or equal to the length of the source string. For example, "Good", "Morn", "od Morni," and "Good Morni" are all substrings of "Good Morning".

Use the strloc function to find out where a particular substring occurs in a source string. This function returns the starting character position of the substring in the source string.

If the substring can be found in the source string more than once, the function returns the starting position of its first occurrence. If the substring is not found, or if the substring is longer than the source string, the function returns zero.

Use the following syntax:

strloc (source_string, sub_string)

where:

source_string

is the string in which to search for the substring.

sub_string

is the string to search for.

This example returns the value 6, because the substring begins in position 6 of the source string.

strloc ("Good Morning", "Morning")

substr

This function extracts a substring from a source string, thereby creating a new string. You specify the source string and the character position where the extraction begins. In addition, you can optionally supply the number of characters to be extracted; if you do not provide this information, the extraction stops at the end of the source string.

Use the following syntax:

substr (source_string, start_pos {, length})

where:

source_string

is the source string.

start_pos

is the character position at which extraction begins.

length

is the number of characters to be extracted (for example, the length of the extracted string).

The following example returns the string "Morning". Note that the enclosing quotes do not count as part of the string.

substr ("Good Morning!", 6, 7)

This function can also extract zero-length strings (strings with no characters between the quotes, for example,"") at a position of one more than the length of a string. This usage is helpful for algorithms which have zero-length strings as an edge condition. You can extract characters from positions 1 through the last character of the string +1; so, positions 1 and the length of the string +1 are edge conditions.

Runtime errors occur if:

strins

This function inserts one string into another string, thereby creating a new string. The values of the source string and insertion string do not change. Use the following syntax:

strins (source_string, insert_string, start_pos)

where:

source_string

is the string that will be combined with insert_string to produce a new string.

insert_string

is the string that will be combined with source_string to produce a new string.

start_pos

is the position in source_string at which the insertion begins; the value ranges from 1 to the length of source_string + 1.

A runtime error occurs if you specify a value less than 1 or greater than source_string + 1.

In the example below, let's say that insert_str = "Mr. Jones, " and that source_str = "Good morning, what a lovely day."

strins (source_str, insert_str, 15)

With the above-specified values, the function returns the following string:

Good morning, Mr. Jones, what a lovely day.

substitute

This function creates a new string by replacing one substring with another inside a specified source string. Use the following syntax:

substitute (source_string, unwanted_string, replacement_string)

where:

source_string

is the string that contains unwanted_string.

unwanted_string

is a substring of source_string.

replacement_string

is the string that will replace unwanted_string to create a new string.

In the following example, every occurrence of "abc" within TestString is replaced with "wxyz". The replacement occurs left-to-right.

substitute (TestString, "abc", "wxyz")

If TestString = "abcdabcd", the function returns the following new string:

wxyzdwxyzd

Note that the contents of the source string, the unwanted string, and the replacement string do not change.

TIP To search for and replace individual characters rather than a substring, use the translate function.

ltrim, rtrim, and trim

These functions create a new string by copying an existing source string and deleting blanks or specified characters from the beginning (ltrim), end (rtrim), or both ends (trim) of the new string. In all cases, the source string remains unchanged.

Use the following syntax for all three functions:

ltrim (string_expr{, trim_char}opt)

rtrim (string_expr{, trim_char}opt)

trim (string_expr{, trim_char}opt)

where:

string_expr

is the source string expression to copy.

trim_char

is an optional parameter that specifies the characters to be deleted from the new string. If you do not specify this parameter, blanks are deleted.

Depending on the function, the deletion starts at either the beginning, end, or both ends of the new string and continues until a character not specified in trim_char (or a non-blank character) is found.

The following example deletes all leading blanks from LastName. In the example, LastName = "  Smith" and the returned new string is "Smith".

ltrim (LastName)

The example below deletes the character "&" from the end of the string called Status. If Status = "OpenXY&Z&&&&", the returned new string is "OpenXY&Z".

rtrim (Status, "&")

The following example deletes all trailing digits from AccountName.

rtrim (AccountName, "0123456789")

is_number

This function determines whether the specified string expression evaluates to a float or integer value. The is_number function returns the boolean value true if the entire string (not just a substring) evaluates to a float or integer and false otherwise.

Use the following syntax:

is_number (expr)

where expr is a string expression.

The following three examples return true, false, and false, respectively.

is_number ("123.4")

is_number ("no")

is_number ("abc63j")

is_time

This function determines whether the specified string expression evaluates to a time value of a specified format. The is_time function returns the boolean value true if the entire string (not just a substring) evaluates to a time that is formatted as specified; otherwise, the function returns a value of false.

Use the following syntax:

is_time (str_expr, date_time_mask)

where:

str_expr

is a string expression.

date_time_mask

is a date/time mask that specifies the format which str_expr must use in order for is_time to return a value of true.

In the following examples, the first example returns false, and the second example returns true. In both examples, the convert function is used to convert the string expressions to the time data type so that the is_time function can be evaluated.

is_time ("nov121995", "mm/dd/yyyy")

is_time ("11/12/1995", "mm/dd/yyyy")

timetostring

This function converts a time expression to a string expression, using the format you specify. Use the following syntax:

timetostring (time_expr, date_time_mask)

where:

time_expr

is an expression of type time.

date_time_mask

is a date/time mask that determines the format of the timetostring function's output.

The following example converts the system time to a string. The string is formatted using the specified date/time mask.

timetostring (systemtime(), "mm/dd/yyyy hh:tt:ss am")

In the above example, if systemtime() is equivalent to July 1, 1985 at 6 seconds past 12:45 p.m., the string output is:

7/1/1985 12:45:06 pm

See information about the systemtime() function.

stringtotime

This function converts strings to times, using the format you specify. Use the following syntax:

stringtotime (string_expr, date_time_mask)

where:

string_expr

is an expression of type string.

date_time_mask

is a date/time mask that specifies the time format to use for the conversion.

In the following example, HostTime is a string variable.

stringtotime (HostTime, "mon. dd, yyyy hh:tt:ss am")

If HostTime = "Jul. 1, 1985 12:45:06 pm", then the resulting output is the time value equivalent to July 1, 1985 at 6 seconds past 12:45 p.m.

encode, decode

These function convert strings into meaningless strings for the purpose of disguising the contents. Both the encode and the decode must be in the same workflow.

The decode function can be used to convert a string expression containing multiple encoded strings.

encode (string_expr)

decode (string_expr)

where:

string_expr

is an expression of type string. The string cannot be a literal.