String Functions


  • STRLEN Retrieves the length of a string.
  • STRLENB Retrieves the length of a string in bytes.
  • LOWER Converts all uppercase letters in a string string to lowercase.
  • UPPER Converts string to uppercase.
  • UCFIRST Makes a string's first character uppercase.
  • UCWORDS Converts the first character of each word in a string to uppercase.
  • CODE Returns the code number of character.
  • CHAR Returns the character specified by the code number.

  • STRSIMILAR Calculates the similarity between two strings.
  • STRINSERT Inserts a sub-string at the given position within string.
  • STRRETP Repeats string a given number of times.
  • JOIN Joins array elements with a string.
  • PADLEFT Pads a string with leading string to a certain length.
  • PADRIGHT Pads a string with trailing string to a certain length.
  • FORMAT_MONEY Formats a number as a currency string for a locale specified.
  • FORMAT_DATE Returns the formatted date string.

  • REPLACE Replaces occurrences of the search string with the replacement string.
  • REPLACE_M Replaces multiple substrings.
  • REPLACE_SUB Locates and replaces a part of string.
  • TRIMLEFT Removes whitespace from the beginning of a string.
  • TRIMRIGHT Removes whitespace from the end of a string.
  • TRIM Removes spaces from the beginning and end of a string.
  • REMOVECHAR Removes characters in a string.
  • STRCLEAN Removes all nonprintable characters from string.
  • LEFT Returns the specified number of characters from the begging of a string.
  • LEFTB Returns the specified number of characters from the begging of a string in bytes.
  • RIGHT Returns the rightmost characters from a string value.
  • RIGHTB Returns the rightmost characters from a string value in bytes.
  • MID Returns a specific number of characters from a string string starting at the position you specify.
  • MIDB Returns a specific number of characters from a string string starting at the position you specify in bytes.
  • STRPICK Splits the string into substrings, then pick one of them.
  • STRDIFFER Gets the difference between two strings.

  • STRSTR Finds the first occurrence of a string.
  • STRSTRI Finds the first occurrence of a case-insensitive string.
  • STRPOS Finds the position of the first occurrence of a substring in a string.
  • STRPOSI Finds the position of the first occurrence of a case-insensitive substring in a string.
  • RSTRPOS Finds the position of the last occurrence of a substring in a string.
  • RSTRPOSI Finds the position of the last occurrence of a case-insensitive substring in a string.
  • BASE64_ENCODE Encodes a string with MIME base64.
  • BASE64_DECODE Decodes a string with MIME base64.
  • MD5 Calculates the md5 hash of a string.
  • HMAC_SHA1 Calculates HMAC-SHA1 of a string.
  • SHA1 Calculates SHA1 of a string.
  • AESENC Encrypts a string with AES256.
  • AESDEC Decrypts a AES256 encrypted string.
  • URLENC Encodes a string to be used in a query part of a URL.
  • URLDEC Decodes a Url-encoded string.
  • URLPARSE Parses a URL and retrieves a component.

  • REGEXP_FIND Searches a string for a match against a regular expression, and returns the matches.
  • REGEXP_INDEXOF Returns the zero-based index of the first occurrence of the specified pattern in a string.
  • REGEXP_REPLACE Searches a string for a regular expression, and returns a new string where the specified values are replaced.

STRLEN

Retrieves the length of a string.

string STRLEN( string )

STRLENB

Retrieves the length of a string in MBCS (Multi-byte character set).

string STRLENB( string )

LOWER

Converts all uppercase letters in a string string to lowercase.

string LOWER( string )

UPPER

Converts string to uppercase.

string UPPER( string )

UCFIRST

Makes a string's first character uppercase.

string UCFIRST( string )

UCWORDS

Converts the first character of each word in a string to uppercase.

string UCWORDS( string )

CODE

Returns the code number of character.

Number CODE( string ,[pos] )

Note: The parameter pos is optional, its default value is 0.

Examples:

%=CODE( "ABC" )% Returns number 65
%=CODE( "ABC", 1 )% Returns number 66

CHAR

Returns the character specified by the code number.

string CHAR( number )

Examples:

%=CHAR( 65 )% Returns number "A"

STRSIMILAR

Calculates the similarity between two strings.

number STRSIMILAR( string1, string2 )

Examples:

%=STRSIMILAR( 'abc', 'abc' )% Returns number 100
%=STRSIMILAR( 'abc', 'abcd' )% Returns number 75

STRINSERT

Inserts a sub-string at the given position within string..

string STRINSERT( string, pos, string_to_insert )

A negative value of the parameter pos is used to specify the position from the end of the string.

Examples:

%=STRINSERT( "This is a sample", 4, "BBB" )% Returns string "ThisBBB is a sample"
%=STRINSERT( "This is a sample", -2, "BBB" )% Returns string "This is a sampBBBle"

STRREPT

Repeats string a given number of times.

string STRREPT( string, num_times )

JOIN

Joins array elements with a string.

string JOIN( string, separator )

Examples:

v_array[0] = abc
v_array[1] = def
%=JOIN(v_array,",")% Returns the string "abc,def"

PADLEFT

Pads a string with leading string to a certain length.

string PADLEFT( string, pad_length, pad_string, [is_repeat_mode] )

The default value of the parameter is_repeat_mode is FALSE.

Examples:

%=PADLEFT( "This", 10, "0123" )% Returns the string "0123012This"
%=PADLEFT( "This", 3, "bc-", TRUE)% Returns the string "bc-bc-bc-This"

PADRIGHT

Pads a string with trailing string to a certain length.

string PADRIGHT( string, pad_length, pad_string, [is_repeat_mode] )

The default value of the parameter is_repeat_mode is FALSE.

Examples:

%=PADRIGHT( "This", 10, "0" )% Returns the string "This000000"
%=PADRIGHT( "This", 3, "-bc", TRUE)% Returns the string "This-bc-bc-bc"

FORMAT_MONEY

Formats a number as a currency string for a locale specified.

string FORMAT_MONEY( number )

Note: The function will use the current local settings of Windows to convert the number.

FORMAT_DATE

Returns the formatted date string.

string FORMAT_DATE( datetime, [format_string] )

Note: About the format string, you please refer to the action Format Date and Time.

Examples:

%=FORMAT_DATE( now, "yyyy-MM-dd" )% Returns string "2013-10-1"

REPLACE

Replaces all occurrences of the search string with the replacement string.

string REPLACE( string, find_string, new_string )

Note: This function can be used to remove all occurrences of the search string. The comparison is case-sensitive.

Examples:

%=REPLACE( "This is a sample", "is", "IS" )% Returns string "ThIS IS a sample"
%=REPLACE( "This is a sample", "is", "" )% Returns string "Th a sample"

REPLACE_M

Replaces multiple substrings at a time.

string REPLACE_M( string, substrs, replaces, [,is_replace_all], [,separator] )

The default value of the parameter is_replace_all is TRUE, and the separator is the semi-colon character.

Examples:

%=REPLACE_M( "This is a sample", "is;sample", "IS;SAMPLE")% Returns string "ThIS IS a SAMPLE"

%=REPLACE_M( "2015-10.20", "-|.", "/|XXX", TRUE, "|" )% Uses the separator |, returns a string "2015/10XXX20"

REPLACE_SUB

Splits the string into a sequence of sub-strings by the separator, then replaces one of the sub-strings.

string REPLACE_SUB( string, separator, which_part, replace_text )

The parameter which_part is zero based.

Examples:

%=REPLACE_SUB( "202.120.22.10", ".", 3, "255" )% Returns "202.120.22.255"
%=REPLACE_SUB( "This is a sample", " ", 2, "A" )% Returns "This is A sample"

TRIMLEFT

Removes whitespace from the beginning of a string.

string TRIMLEFT( string )

TRIMRIGHT

Removes whitespace from the end of a string.

string TRIMRIGHT( string )

TRIM

Removes spaces from the beginning and end of a string.

string TRIM( string )

REMOVECHAR

Removes one or more characters from a string.

string REMOVECHAR( string, characters )

Examples:

%=REMOVECHAR( "202.120.22.10", "." )% Returns "2021202210"
%=REMOVECHAR( "This is a sample", "isa" )% Returns "Th mple"
%=REMOVECHAR( "This 1is 2a 3sample", "1234567890" )% Returns "This is a sample"

STRCLEAN

Removes all nonprintable characters from string.

string STRCLEAN( string )

Note: Any character that is not a displayable ASCII character is removed from the string, included CRLF.

LEFT

Returns the specified number of characters from the begging of a string.

string LEFT( string, num_characters )

LEFTB

Returns the specified number of characters from the begging of a string in MBCS (Multi-byte character set).

string LEFTB( string, num_characters )

RIGHT

Returns the rightmost characters from a string value.

string RIGHT( string, num_characters )

RIGHTB

Returns the rightmost characters from a string value in MBCS (Multi-byte character set).

string RIGHTB( string, num_characters )

MID

Returns a specific number of characters from a string string starting at the position you specify.

string MID( string, pos, [num_characters] )

Note the parameter num_characters is optional, the default value is 1, omiting it does mean get all characters from the position pos to the end of the string.

Examples:

%=MID( "This is a sample", 1, 3 )% Returns the string "his"
%=MID( "This is a sample", 3 )% Returns the string "s is a sample"

MIDB

Returns a specific number of characters from a string string starting at the position you specify in in MBCS (Multi-byte character set). The default value of the parameter num_characters is 1.

string MIDB( string, pos, [num_characters] )

STRPICK

Splits the string into a sequence of sub-strings, then pick one of them.

string STRPICK( string, separator, index_of_substring )

The parameter index_of_substring is zero based. The parameter separator can be a single character or a string.

Examples:

%=STRPICK( "abc;cdef;xyz", ";", 1 )% Returns "cdef"
%=STRPICK( "abc--cdef--xyz", "--", 1 )% Returns "cdef"

STRDIFFER

Gets the difference between two strings.

string STRDIFFER( string1, string2, [return_difference] )

If the parameter return_difference is TRUE (default value TRUE), the characters in string1 which are not in the string2 will be returned. If the parameter return_difference is FALSE, the characters in string1 which are in the string2 will be returned.

Examples:

%=STRDIFFER( "abc", "abcdefg", FALSE )% Returns the string "abc"
%=STRDIFFER( "abcdefg", "abc", TRUE )% Returns the string "defg"
%=STRDIFFER( "abc", "abcdefg", TRUE )% Returns the string ""

STRSTR

Finds the first occurrence of a string.

string STRSTR( string, find_string )

Note: The comparison is case-sensitive. This function returns an empty string if failed.

Examples:

%=STRSTR( "This is a sample", "is" )% Returns string "is is a sample"
%=STRSTR( "This is a sample", "IS" )% Returns empty string

STRSTRI

Finds the first occurrence of a case-insensitive string.

string STRSTRI( string )

STRPOS

Finds the position of the nth occurrence of a substring in a string.

Number STRPOS( string, find_string, [index_of_sequence] )

The default value of the parameter index_of_sequence is 0.
Note: This function returns -1 if failed. The comparison is case-sensitive.

STRPOSI

Finds the position of the nth occurrence of a case-insensitive substring in a string.

Number RSTRPOS( string, find_string, [index_of_sequence] )

The default value of the parameter index_of_sequence is 0.

RSTRPOS

Reverses find the index position of the nth occurrence of a substring in a string. The default value of the parameter index_of_sequence is 0.

Number RSTRPOS( string, find_string, [index_of_sequence] )

Note: This function returns -1 if failed. The comparison is case-sensitive.

RSTRPOSI

Reverses find the position of the nth occurrence of a case-insensitive substring in a string.

Number RSTRPOSI( string, find_string, [index_of_sequence] )

The default value of the parameter index_of_sequence is 0.

BASE64_ENCODE

Encodes a string with MIME base64.

string BASE64_ENCODE( string_or_binary_data )

BASE64_DECODE

Decodes a string with MIME base64.

string BASE64_DECODE( string [, data_type])

The parameter data_type can be one of the follow:

  • 0 (Default value) -- The decoded data is a string.
  • 1 -- The decoded data is an ANSI string.
  • 2 -- The decoded data is a binary data.

Note: The parameter string should be a valid based64 encoded string.

MD5

Calculates the md5 hash of a string.

string MD5( string )

HMAC_SHA1

Calculates HMAC-SHA1 of a string.

string HMAC_SHA1( string, key )

Examples:

%=HMAC_SHA1("abcdefg","123456")% Returns string "04B09561DB1D5AA5E8A6ED6228D9F8A4F933AD1E"

SHA1

Calculates SHA1 of a string.

string SHA1( string )

Examples:

%=SHA1("abcdefg")% Returns string "2FB5E13419FC89246865E7A324F476EC624E8740"

AESENC

Encrypts a string with AES256.

string AESENC( string, password )

Examples:

%=AESENC( "abc", "abcdefg" )% Returns string "FB2EB51438240E3A59825379C6A26461CE1708AD0F2FB37F0CF5A34BD7 740B0DCE1708AD0F2FB37F0CF5A34BD7740B0DCE1708AD0F2FB37F0CF5A34BD7740B0D"

AESDEC

Decrypts an encrypted string by AES256.

string AESDEC( string, password )

URLENC

Encode a string by URL specification.

string URLENC( string )

URLDEC

Decodes a URL encoded string.

string URLDEC( string )

URLPARSE

Parses a URL and retrieves a component.

string URLPARSE( url, parseaction )

The parameter parseaction specifies the information to parse from the URL, it can be one of the follow:

  • DOMAIN Return the domain name for this URL.
  • SITE Return the site associated with this URL.
  • SCHEMA Retrieve the schema for this URL.

REGEXP_FIND

Searches a string for a match against a regular expression, and returns the matches.

string REGEXP_FIND( string, pattern[, flags, separator, offset] )

Parameters:

  • string The string to search for a match.
  • pattern The regular expression pattern to match.
  • flags Provides options for matching. The value is a string containing any combination of the following values:
    • i Performs a match without regard to case.
    • g Performs a global match (find all matches rather than stopping after the first match).
  • separator A string, if flags includes g, all matches will be joined by this string. The default separator is the semicolon ;
  • offset The search starting position. If?offset?is a negative number, the position starts counting from the end of the string.

Examples:

%=REGEXP_FIND( "abcdef 123", "\d+" )% Returns a string "123"

%=REGEXP_FIND( "abcdef 123", "a[a-z]+" )% Returns a string "abcdef"

%=REGEXP_FIND( "abcdef 123", "A[a-z]+", "i")% Uses the flag "i", it will return a string "abcdef"

%=REGEXP_FIND( "abcdef 123 abc 678", "\d+", "g")% Returns a string "123;678"

%=REGEXP_FIND( "abcdef 123 abc 678", "\d+", "g", "-")% Returns a string "123-678"

%=REGEXP_FIND( "abcdef 123 abc 678", "\d+", "", "", -3 )% Returns a string "678"

REGEXP_INDEXOF

Returns the zero-based index of the first occurrence of the specified pattern in a string, or -1 if it is not.

number REGEXP_INDEXOF( string, pattern[, offset, flags] )

Parameters:

  • string The string to search for a match.
  • pattern The regular expression pattern to match.
  • offset The search starting position. If?offset?is a negative number, the position starts counting from the end of the string.
  • flags Provides options for matching. The value is a string containing any combination of the following values:
    • i Performs a match without regard to case.
    • g Performs a global match (find all matches rather than stopping after the first match).

Examples:

%=REGEXP_INDEXOF( "abc123", "\d+" )% Returns 3

%=REGEXP_INDEXOF( "abc123", "\d+", 2 )% Returns 3

%=REGEXP_INDEXOF( "abc1234", "\d+", -2 )% Returns 5 as the offset is a negative number.

%=REGEXP_INDEXOF( "8 abcdef 123", "\d+", 3)% Uses the flag "i", it will return 9

%=REGEXP_INDEXOF( "8 abcdef 123", "A[a-z]+", 0, "i")% Uses the flag "i", it will return 2

REGEXP_REPLACE

Searches a string for a regular expression, and returns a new string where the specified values are replaced.

string REGEXP_REPLACE( string, pattern[, new_substr, flags, offset] )

Parameters:

  • string The string to search for a match.
  • pattern The regular expression pattern to match.
  • new_substr The string to substitute the match.
  • flags Provides options for matching. The value is a string containing any combination of the following values:
    • i Performs a match without regard to case.
    • g Performs a global match (find all matches rather than stopping after the first match).
  • offset The search starting position. If?offset?is a negative number, the position starts counting from the end of the string.

Examples:

%=REGEXP_REPLACE( "abc123", "\d+", "999")% Returns "abc999"

%=REGEXP_REPLACE( "8 abcdef 123", "\d+", "", "g")% Returns " abcdef"

%=REGEXP_REPLACE( "abc123", "(\d+)", "$1-999" )% Returns "abc123-999"

%=REGEXP_REPLACE( "abc123", "(\d+)", "999", "", -2 )% Returns "abc1999"