String Functions
- Format
- FormatMessage
- HashString
- InStr
- InStrRev
- LCase
- Left
- Len
- LTrim
- Trim
- Mid
- Replace
- Right
- RTrim
- Space
- StrComp
- String
- StringLike
- StrReverse
- TitleCase
- UCase
Format
Description
Returns a string formatted according to instructions contained in a format String expression.
Syntax
- Format( expression, style )
Arguments
- expression
- Any valid expression.
- style
- Optional. A valid named or user-defined format String expression.
A number of good references can be found on the web for the format function.
Comprehensive guide to the format function can be found on the Microsoft website: https://support.office.com/en-us/article/Format-Function-6F29D87B-8761-408D-81D3-63B9CD842530
Example
Dim MyTime, MyDate, MyStr
MyTime = #17:04:23#
MyDate = #January 27, 1993#
' Returns current system time in the system-defined long time format.
MyStr = Format(Time, "Long Time")
' Returns current system date in the system-defined long date format.
MyStr = Format(Date, "Long Date")
MyStr = Format(MyTime, "h:m:s") ' Returns "17:4:23".
MyStr = Format(MyTime, "hh:mm:ss AMPM") ' Returns "05:04:23 PM".
MyStr = Format(MyDate, "dddd, mmm d yyyy") ' Returns "Wednesday,' Jan 27 1993".
' If format is not supplied, a string is returned.
MyStr = Format(23) ' Returns "23".
' User-defined formats.
MyStr = Format(5459.4, "##,##0.00") ' Returns "5,459.40".
MyStr = Format(334.9, "###0.00") ' Returns "334.90".
MyStr = Format(5, "0.00%") ' Returns "500.00%".
MyStr = Format("HELLO", "<") ' Returns "hello".
MyStr = Format("This is it", ">") ' Returns "THIS IS IT".
FormatMessage
Description
Provides an easy means to create a string which is parameterised with values.
This function is useful when constructing messages to be used in the WriteToLog or Lookup Function, but can be used anywhere where a string needs to be built dynamically.
Syntax
- FormatMessage ( message, values )
Arguments
- Message
- The message to format. A parameter is specified by a ‘%’ symbol followed by the incrementing value of the parameter. For example the first parameter is %1, the second parameter is %2 and so on.
- values
- The value(s) to replace the parameters within the message.
- One or more values to parameterise the message. The values are either a single value or an array of values.
Example
Format a string with a single parameter.
FormatMessage(“Customer %1 is not valid.”, %CustomerId)
Format a string with multiple parameters.
FormatMessage(“The %1 customer with email %2 is not valid.”, Array(%CustomerType, %CustomerId))
HashString
Description
Allows you to calculate a hash for one or more strings.
Syntax
- HashString( hashMethod, strings)
Arguments
- hashMethod
- The method to hash the string. It allows the following values: md5, sha1, sha256, sha284, sha512.
- strings
- The strings to be hashed. If multiple values are passed, the return value will be a hash of the concatenated strings.
Example
Hash a single string using the sha1 method.
HashString(“sha1”, %Url)
Produce an sha284 hash using multiple strings.
HashString(“sha284”, Array(%Url, %UserId, %CurrentDateTime))
InStr
Description
Returns the position of the first occurrence of a string in another string.
Syntax
- InStr( [start], string_being_searched, string2, [compare] )
Arguments
- start
- Optional. It is the starting position for the search. If this parameter is omitted, the search will begin at position 1.
- string_being_searched
- The string that will be searched.
- string2
- The string to search for.
- compare
- Optional. This is the type of comparison to perform. The valid choices are:
Value |
Explanation |
---|---|
0 |
Binary comparison. |
1 |
Textual comparison. |
InStrRev
Description
Returns the position of the first occurrence of a string in another string, starting from the end of the string.
Syntax
- InstrRev ( string_being_searched, string2 [, start [ , compare] ] )
Arguments
- string_being_searched
- The string that will be searched.
- string2
- The string to search for.
- start
- Optional. It is the starting position for the search. If this parameter is omitted, the search will begin at position -1 which is the last character position.
- compare
- Optional. This is the type of comparison to perform. The valid choices are:
Value |
Explanation |
---|---|
0 |
Binary comparison. |
1 |
Textual comparison. |
LCase
Description
Converts a string to lower-case.
Syntax
- LCase( text )
Arguments
- text
- The string that you wish to convert to lower-case.
Left
Description
Returns a substring from a string, starting from the left-most character.
Syntax
- Left( text, number_of_characters )
Arguments
- text
- The string that you wish to extract from.
- number_of_characters
- Indicates the number of characters that you wish to extract starting from the left-most character.
Len
Description
Returns the length of the specified string.
Syntax
- Len( text )
Arguments
- text
- The string to return the length for.
LTrim
Description
Removes leading spaces from a string.
Syntax
- LTrim( text )
Arguments
- text
- The string that you wish to remove leading spaces from.
Trim
Description
Returns a text value with the leading and trailing spaces removed.
Syntax
- Trim( text )
Arguments
- text
- The text value to remove the leading and trailing spaces from.
Mid
Description
Extracts a substring from a string (starting at any position).
Syntax
- Mid( text, start_position, number_of_characters )
Arguments
- text
- The string that you wish to extract from.
- start_position
- Indicates the position in the string that you will begin extracting from. The first position in the string is 1.
- number_of_characters
- Indicates the number of characters that you wish to extract.
Replace
Description
Replaces a sequence of characters in a string with another set of characters.
Syntax
- Replace( string, find, replacewith, [,start[,count[,compare]]]) )
Arguments
- string
- The string to be searched.
- find
- Part of the string that will be replaced.
- replacewith
- The replacement substring.
- start
- Optional. Is the position in string to begin replacing characters.
- count
- Optional. Specifies the number of substitutions to perform.
Default value is -1, which means make all possible substitutions.
- Optional. Specifies the number of substitutions to perform.
- compare
- Optional. Numeric value indicating the kind of comparison to use when evaluating strings. If omitted, a binary comparison is performed. The valid choices are:
Value |
Explanation |
---|---|
0 |
Binary comparison. |
1 |
Textual comparison. |
Right
Description
Extracts a substring from a string starting from the right-most character.
Syntax
- Right( text, number_of_characters )
Arguments
- text
- The string that you wish to extract from.
- number_of_characters
- Indicates the number of characters that you wish to extract starting from the right-most character.
RTrim
Description
Removes trailing spaces from a string.
Syntax
- RTrim( text )
Arguments
- text
- The string that you wish to remove trailing spaces from.
Space
Description
Returns a string with a specified number of spaces.
Syntax
- Space( number )
Arguments
- number
- The number of spaces to be returned.
StrComp
Description
Returns a value indicating the result of a string comparison.
Syntax
- StrComp(string1, string2[, compare])
Arguments
- string1
- Any valid string expression.
- string2
- Any valid string expression.
- compare
- Optional. Numeric value indicating the kind of comparison to use when evaluating strings. If omitted, a binary comparison is performed. The valid choices are:
Value |
Explanation |
---|---|
0 |
Binary comparison. |
1 |
Textual comparison. |
String
Description
Returns a repeating character string of the length specified.
Syntax
- String(number, character)
Arguments
- number
- The length of the returned string. If number contains Null, Null is returned.
- character
- The character code specifying the character or string expression whose first character is used to build the return string. If character contains Null, Null is returned. If you specify a number for character greater than 255, String converts the number to a valid character code using the modulus of 256.
StringLike
Description
Compares a string against a pattern. If the value in string satisfies the pattern contained in pattern, result is True. If the string does not satisfy the pattern, result is False. If both string and pattern are empty strings, the result is True.
Syntax
- StringLike( string, pattern )
Arguments
- string
- A string expression.
- pattern
- The pattern to compare the string to.
- Built-in pattern matching provides a versatile tool for string comparisons.
- The pattern-matching features allow you to match each character in string against a specific character, a wildcard character, a character list, or a character range. The following table shows the characters allowed in pattern and what they match.
Characters in pattern |
Matches in string |
---|---|
? | Any single character |
* | Zero or more characters |
# | Any single digit (0–9) |
[charlist] | Any single character in charlist |
[!charlist] | Any single character not in charlist |
|
|
StrReverse
Description
Returns a string in which the character order of a specified string is reversed.
Syntax
- StrReverse(string)
Arguments
- string
- The string whose characters are to be reversed. If string is a zero-length string (""), a zero-length string is returned. If string is Null, an error occurs.
TitleCase
Description
Returns a string where the first letter to every word is uppercase and all other characters are left as lowercase.
Syntax
- TitleCase( string )
Arguments
- string
- The string to be formatted.