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.

Example

' Position of the first separator in a supplier-part-
' colour item code.
InStr(%ItemCode, "-")   ' "ACME-4471-BLU" returns 5

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.

Example

' The LAST separator, for taking everything after it.
Dim p
p = InStrRev(%ItemCode, "-")
Mid(%ItemCode, p + 1)   ' "ACME-4471-BLU" returns "BLU"

LCase¶

Description

Converts a string to lower-case.

Syntax

LCase( text )

Arguments

text
- The string that you wish to convert to lower-case.

Example

LCase(%WarehouseCode)   ' "MAIN" returns "main"

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.

Example

' The supplier prefix.
Left(%ItemCode, 4)   ' "ACME-4471-BLU" returns "ACME"

Len¶

Description

Returns the length of the specified string.

Syntax

Len( text )

Arguments

text
- The string to return the length for.

Example

' Guard a fixed-width ERP field before writing to it.
Len(%ItemCode)   ' "ACME-4471-BLU" returns 13

LTrim¶

Description

Removes leading spaces from a string.

Syntax

LTrim( text )

Arguments

text
- The string that you wish to remove leading spaces from.

Example

' Leading spaces only. Fixed-width and CSV sources are
' full of them.
LTrim("   ACME001")   ' Returns "ACME001"

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.

Example

' Both ends. The one to reach for on almost any inbound
' text field.
Trim(%Description)   ' "  Blue widget  " returns "Blue widget"

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.

Example

' The part number, four characters in from position 6.
Mid(%ItemCode, 6, 4)   ' "ACME-4471-BLU" returns "4471"

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.
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.

Example

Replace(%ItemCode, "-", "/")   ' "ACME-4471-BLU" returns "ACME/4471/BLU"

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.

Example

' The colour suffix.
Right(%ItemCode, 3)   ' "ACME-4471-BLU" returns "BLU"

RTrim¶

Description

Removes trailing spaces from a string.

Syntax

RTrim( text )

Arguments

text
- The string that you wish to remove trailing spaces from.

Example

' Trailing spaces only -- what a CHAR(20) column hands
' you.
RTrim("MAIN     ")   ' Returns "MAIN"

Space¶

Description

Returns a string with a specified number of spaces.

Syntax

Space( number )

Arguments

number
- The number of spaces to be returned.

Example

' Pad a code out to a fixed width for a flat-file
' writer.
%WarehouseCode & Space(10 - Len(%WarehouseCode))   ' "MAIN" returns "MAIN      "

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.

Example

' Returns -1, 0 or 1. Pass 1 as the third argument for a
' case-insensitive comparison; the default, 0, is binary
' and case-sensitive.
StrComp(%WarehouseCode, "NORTH")        ' "MAIN" returns -1
StrComp(%WarehouseCode, "main", 1)      ' "MAIN" returns 0

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.

Example

' A run of one character -- zero-fill a reference to a
' fixed width.
String(5 - Len(%Qty), "0") & %Qty   ' 10 returns "00010"

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 comparison is case-sensitive, and the whole of string must match the pattern - it is not a "contains" or "starts with" test. 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 other character	Itself. Only ? and * have a special meaning; the # and [charlist] wildcards of VBScript's Like operator are not supported and match literally.

Example

' IMan-specific. The pattern is the SECOND argument.
' Only * (any run of characters) and ? (exactly one) are
' supported -- not the # or [charlist] of VBScript's
' Like operator -- and it is case-sensitive. The whole
' value must match, so the pattern is implicitly
' anchored.
StringLike(%ItemCode, "ACME-*")        ' "ACME-4471-BLU" returns True
StringLike(%ItemCode, "ACME-????-*")   ' "ACME-4471-BLU" returns True
StringLike(%ItemCode, "acme-*")        ' Returns False -- case matters
StringLike(%ItemCode, "ACME")          ' Returns False -- not a prefix test

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.

Example

StrReverse(%WarehouseCode)   ' "MAIN" returns "NIAM"

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.

Example

' IMan-specific. The value is lowercased BEFORE being
' title-cased, so an all-caps name is normalised rather
' than left alone.
TitleCase(%CustomerName)   ' "acme trading ltd" returns "Acme Trading Ltd"
TitleCase("ACME TRADING LTD")   ' Also returns "Acme Trading Ltd"

UCase¶

Description

Converts a string to all upper-case.

Syntax

UCase( text )

Arguments

text
- The string that you wish to convert to upper-case.

Example

UCase(%CustomerCode)   ' "acme001" returns "ACME001"