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 a format string.

Syntax

Format( expression, style )

Arguments

• expression
  • Any valid expression.
• style
  • Optional. A valid named or user-defined format String expression.

Microsoft's own guide to the format function covers the named and user-defined formats in full: 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

Builds a string from a message with numbered parameters and the values to put in them.

It is useful for building messages for WriteToLog or the Lookup Function, and anywhere else you need to build a string dynamically.

Syntax

FormatMessage ( message, values )

Arguments

• Message
  • The message to format. Write each parameter as % followed by its number: the first is %1, the second %2 and so on.
• values
  • The value(s) to replace the parameters within the message.
  • 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 several parameters.

FormatMessage(“The %1 customer with email %2 is not valid.”, Array(%CustomerType, %CustomerId))

HashString

Description

Calculates a hash of one or more strings.

Syntax

HashString( hashMethod, strings)

Arguments

• hashMethod
  • The hash method: md5, sha1, sha256, sha384 or sha512.
• strings
  • The strings to hash. If you pass several, HashString returns a hash of the strings concatenated.

Example

Hash a single string using the sha1 method.

HashString(“sha1”, %Url)

Produce a sha384 hash of several strings.

HashString("sha384", 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. The position to start the search at. If omitted, the search starts at position 1.
• string_being_searched
  • The string to search.
• string2
  • The string to search for.
• compare
  • Optional. 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 to search.
• string2
  • The string to search for.
• start
  • Optional. The position to start the search at. If omitted, the search starts at position -1, the last character.
• compare
  • Optional. 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 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 to extract from.
• number_of_characters
  • The number of characters 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 a 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 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 to extract from.
• start_position
  • The position to start extracting from. The first position in the string is 1.
• number_of_characters
  • The number of characters 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 search.
• find
  • The part of the string to replace.
• replacewith
  • The replacement substring.
• start
  • Optional. The position in string to start replacing characters at.
• count
  • Optional. The number of substitutions to make.
    The default, -1, makes every possible substitution.
• compare
  • Optional. A number for the kind of comparison to use. If omitted, the function performs a binary comparison. 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 to extract from.
• number_of_characters
  • The number of characters 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 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 return.

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 the result of a string comparison as a number.

Syntax

StrComp(string1, string2[, compare])

Arguments

• string1
  • Any valid string expression.
• string2
  • Any valid string expression.
• compare
  • Optional. A number for the kind of comparison to use. If omitted, the function performs a binary comparison. 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 string of one character repeated to a given length.

Syntax

String(number, character)

Arguments

• number
  • The length of the returned string. If number contains Null, String returns Null.
• character
  • A character code, or a string whose first character String repeats. If character contains Null, String returns Null. If character is a number greater than 255, String converts it to a valid character code using modulus 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. It returns True if string matches pattern, and False if it does not. If both string and pattern are empty strings, it returns True.

Syntax

StringLike( string, pattern )

Arguments

• string
  • A string expression.
• pattern
  • The pattern to compare the string to.
  • 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 with its characters in reverse order.

Syntax

StrReverse(string)

Arguments

• string
  • The string to reverse. If string is a zero-length string (""), StrReverse returns a zero-length string. If string is Null, an error occurs.

Example

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

TitleCase

Description

Returns a string with the first letter of every word in upper case and every other character in lower case.

Syntax

TitleCase( string )

Arguments

• string
  • The string to format.

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

Syntax

UCase( text )

Arguments

• text
  • The string to convert to upper case.

Example

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