Expression: String Functions

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.

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”

Example - FormatNumber([Cost]*1000,0,"T") to calculate and insert thousand separators:

Left

Left part of a string

Left( string, n)

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

Right

Right part of a string

Right( string, n )

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.

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])

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.   

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)

StrLength ("Apteco") = 6

StrLower

Converts a string to lower case

StrLower( string)

StrLower("Apteco") = "apteco"

StrUpper

Converts a string to upper case

StrUpper( string)

StrUpper("Apteco") = "APTECO"

StrProper

Converts a string to proper case

StrProper( string)

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

 StrProper (“redfern house”) = “Redfern House”

StrReverse

Reverses a string

StrReverse( string)

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.

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.

Note StrContains is case sensitive.  Use StrUpper / StrLower to equalize the case if case insensitive match is required.

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.

StrFind (“Huddersfield Town”,”Town”) = 13

StrFind (“Huddersfield Town”,”Hudd”) = 0

StrFind (“Huddersfield Town”,”Udders”) = -1

Note StrFind is case sensitive.  Use StrUpper / StrLower to equalize the case if case insensitive match is required.

LeftTrim

Removes leading spaces from string.

LeftTrim(" text ")

LeftTrim(" Apteco ") = "Apteco "

Trim

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

Trim(String)

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)

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)

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)

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.

Example: StrShed([Address],4,20,"Proper",",")

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.

Example: StrShredContains([Address],4,20,"Proper","Close","Road")

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

See: https://corporate.tpsonline.org.uk/index.php/tps/technical

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.

Example: Telephone([Telephone],"TPS")

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

e.g. StrStreak(“abbccdde”) will return a

e.g StrStreak(“bbbaccdd”) will return bbb

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.

e.g. StrHash([Email Address],”SHA256”)

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.

e.g. StrCount("banana","an") will return a value of 2

Provides the string representation of the Ascii code specified.

e.g. FromAscii(65)) will return the string A

Provides the code of the Ascii value specified.

e.g. ToAscii("A") will return the code 65

Note: If the string value used is longer than 1 character, this function will return "Missing Value"