String functions

These functions allow string manipulation.

FormatNumber¶

Formats a number to the given precision.

FormatNumber(n,ndp, [use separator])

Formats n, where ndp is the number of decimal places and 'use separator' denotes whether to display thousand separators with "T" or "F" as valid values.

For example, if [Cost] is 56.78:

• AddStr("your bill is £",FormatNumber([Cost],2)) is "Your bill is £56.78"
• AddStr(“Your rounded bill is £”,FormatNumber(Round([Cost]),0) is “Your rounded bill is £56”

Left¶

Left part of a string.

Left( string, n)

For example, Left(“Red Nose Day”,6) = “Red No”

Right¶

Right part of a string.

Right( string, n )

For example, Right(“Red Nose Day”,3) = “Day”

Mid¶

Middle part of a string.

Mid( string, start, length n)

Extracts n characters from the string. The start position is 1-based.

For example, Mid(“Red Nose Day”,5,4) = “Nose”

SubStr¶

Returns a portion of a string.

SubStr( String, Start, Size)

Start is a zero based offset.

If variable Surname = "Pants" in current record:

• SubStr([Surname],1,3) returns "ant"
• SubStr([Surname],0,3) returns "Pan"

AddStr¶

Concatenates strings together.

AddStr([Text],[Text],[Text],…)

AddStr("Dear ",[Title]," ",[Initial]," ",[Surname])

For example, AddStr([Title],” “,[Initial],” “,[Surname]) might return “Mr J Smith”.

StrCompare¶

Compares two strings.

StrCompare( string, string , case_sensitive)

Case_sensitive is an optional numeric parameter. If case_sensitive evaluates to 1 then the comparison is case sensitive. If case_sensitive evaluates to 0 or missing_value or is not provided then the comparison is not case sensitive.

For example, StrCompare( string1, string2) compares two strings alphabetically and returns:

• 1 if [string1] > [string2]
• -1 if [string1] < [string2]
• 0 if [string1] = [string2]

StrLength¶

Gets length of a string.

StrLength( string)

For example, StrLength ("Apteco") = 6

StrLower¶

Converts a string to lower case.

StrLower( string)

For example, StrLower("Apteco") = "apteco"

StrUpper¶

Converts a string to upper case.

StrUpper( string)

For example, StrUpper("Apteco") = "APTECO"

StrProper¶

Converts a string to proper case.

StrProper( string)

StrProper case capitalises the first letter after a space or punctuation character.

For example, StrProper (“redfern house”) = “Redfern House”

StrReverse¶

Reverses a string.

StrReverse( string)

For example, StrReverse("Apteco") = "ocetpA"

StrContains¶

Finds substrings within a string.

StrContains( x, a, b, c, ...)

StrContains searches string x for substrings a, b, c, etc., and returns the index of the first match found, or zero if none are found.

Examples:

• StrContains(“Huddersfield Town”,”City”,”Town”) = 2
• StrContains(“Smith”,”Smith”,”Jones”,”Walker”) = 1
• StrContains(“Huddersfield Town”,”udders”,”cow”,”field”) = 1
• StrContains(“Huddersfield Town”,”Udders”,”Cow”,”Field”) = 0
• StrContains(“[email]”,”hotmail”,”yahoo”,”aol”,”ntlworld”) detects if the email field has any of the listed ISPs.

For example, StrContains(StrUpper(“Huddersfield Town”),”UDDERS”,”COW”,”FIELD”) = 1

StrFind¶

Locates a substring within a string.

StrFind( search_string, target_string)

Returns the zero based start offset of the substring or -1 if not found.

Examples:

• StrFind (“Huddersfield Town”,”Town”) = 13
• StrFind (“Huddersfield Town”,”Hudd”) = 0
• StrFind (“Huddersfield Town”,”Udders”) = -1

LeftTrim¶

Removes leading spaces from string.

LeftTrim(" text ")

For example, LeftTrim(" Apteco ") = "Apteco "

Trim¶

Removes all spaces except for leaving a single space between words.

Trim(String)

For example, Trim(" James Alty Apteco") = "James Alty Apteco"

StrClean¶

Removes all characters from string 1 that are listed in string 2 (case sensitive).

StrClean(String 1, String 2)

For example, StrClean("Apteco","e") = "Aptoc". StrClean([Postcode]," ") = "CV344AP"

StrReplace¶

Replaces all occurrences of A with B in string X (case sensitive).

StrReplace(string X, string A, string B)

For example, StrReplace("Apteco","A","a") = "apteco"

StrNumber¶

Converts a string value to its numeric (double) representation. If the input text does not represent a number, StrNumber exaluates to a missing value.

StrNumber(String)

For example, StrNumber("123") = 123

StrShred¶

Converts a text entry into a delimited, alphabetical list of the constituent words.

The expression format is as follows:

StrShred(Text,MinSize,MaxSize,Case,Delimiter)

The min and max size will determine the number of letters in the words that are returned.

For example, StrShed([Address],4,20,"Proper",",").

StrShredContains¶

Converts a text entry into a delimited list, as per StrShred expression, and determine which words to search for. This will return a 1 for a match on the first word searched for, 2 on the second etc. and a 0 if nothing matches.

The expression format is as follows:

StrShredContains(Text,MinSize,MaxSize,Case,String1,String2...)

Where String1, String2 etc. are the words you are searching for.

For example, StrShredContains([Address],4,20,"Proper","Close","Road")

Telephone¶

Converts a text entry into a number which is compatible with TPS 1.2 "Without Spaces" format.

See TPS technical documentation.

This UK specific option will also remove any 44 prefix from the number.

The expression format is as follows:

Telephone([text],"options")

Where text = a text variable and options = TPS, which is the only option at present.

For example, Telephone([Telephone],"TPS")

StrStreak¶

Allows you to return the first characters of a string until there is a change in characters.

For example:

• StrStreak(“abbccdde”) will return a
• StrStreak(“bbbaccdd”) will return bbb

StrHash¶

Allows you to take a string and apply one of a number of algorithms that will encrypt the string so that it is secure when passed over a network.

For example, StrHash([Email Address],”SHA256”)

StrCount¶

Counts the number of times a target string appears in a search string. This action is case sensitive and works only for the non-overlapping occurrences.

For example, StrCount("banana","an") will return a value of 2

FromAscii¶

Provides the string representation of the Ascii code specified.

For example, FromAscii(65) will return the string A

ToAscii¶

Provides the code of the Ascii value specified.

For example, ToAscii("A") will return the code 65

Combining string functions¶

Combining string functions with missing values functions and logical functions can create powerful expressions.

The following example creates a salutation, like Dear Mr J Smith.

AddStr("Dear ",If(IsMissing([Surname]),"Customer",AddStr(DescOf([Title])," ",[Surname])))

If the surname is missing for the record, the salutation would be Dear Customer.