Using String Functions

The String functions operate on strings and provide related functionality.

ASCIITOCHAR

Converts an Int64 expression representing ASCII values(s) to the equivalent character string. Each byte in the Int64 expression represents one character. As the input of this function is a 64-bit integer, the resulting string is limited to 8 characters.

Examples:

SELECT asciitochar(65) --returns A
SELECT asciitochar(4276803) --returns ABC (0x414243)

ASCIITOWCHAR

Converts an Int64 expression representing Unicode values(s) to the equivalent Unicode character string. Each 2 bytes in the Int64 expression represent one Unicode character. As the input of this function is a 64-bit integer, the resulting string is limited to 4 characters.

Examples:

SELECT asciitowchar(8721) --returns ∑
SELECT asciitowchar(1735478956) --returns 東京

BASE64DECODE

Returns the Base64 encoded version of the input String expression.

Example:

SELECT base64encode('hello') --returns aGVsbG8=

BASE64ENCODE

Decodes the input Base64 encoded String expression.

Example:

SELECT base64decode('aGVsbG8=') --returns hello

CHARTOASCII

Converts a String expression of one or more characters into an Int64 value representing the corresponding ASCII values. Each character in the input String expression represents one byte in the resulting Int64 value. As the output of this function is an Int64 value, the input expression is limited to 8 characters.

Examples:

SELECT chartoascii('A') --returns 65
SELECT chartoascii('ABC') --returns 4276803

COMPARE

Compares two String expressions using the invariant culture, either with case sensitivity or case insensitivity. The syntax is:

compare(first_string_expression, second_string_expression, bool_case_sensitive_comparison)

The result is -1 if the first string is less than the second string, 1 if the first string is greater than the second string, and 0 if the two strings are equal.

Examples:

SELECT compare('a', 'A', true) --returns -1 (case-sensitive)
SELECT compare('A', 'a', true) --returns 1 (case-sensitive)
SELECT compare('a', 'a', true) --returns 0 (case-sensitive)
SELECT compare('a', 'A', false) --returns 0 (case-insensitive)
SELECT compare('diyarbakır', 'DİYARBAKIR', false) --returns -1 (case-insensitive, but because of the Turkish i, the first string is lesser than the second)

COMPARECULTURE

Compares two String expressions using the culture of the collation of the first string, either with case sensitivity or case insensitivity. If the COLLATE keyword is not specified, the culture of the data model’s collation will be used. The syntax is:

compareculture(first_string_expression, second_string_expression, bool_case_sensitive_comparison)

The result is -1 if the first string is less than the second string, 1 if the first string is greater than the second string, and 0 if the two strings are equal.

Examples:

SELECT compareculture('a', 'A', true) --returns -1 (case-sensitive)
SELECT compareculture('A', 'a', true) --returns 1 (case-sensitive)
SELECT compareculture('a', 'a', true) --returns 0 (case-sensitive)
SELECT compareculture('a', 'A', false) --returns 0 (case-insensitive)
SELECT compareculture('diyarbakır' COLLATE tr-tr_ci, 'DİYARBAKIR', false) --returns 0 (case-insensitive, but since the comparison is executed in the Turkish culture, the two strings result equal)

CONCAT

Takes N String expressions and produces a single String value representing the concatenation of the input expressions.

Example:

SELECT concat('a', 'b', 'c') --returns "abc"

CONCAT_WS

Takes a String expression representing a separator and N String expressions to produce the concatenation of the N expressions separated by the specified separator.

This function is the equivalent of the Expression Engine’s join function, however the JOIN keyword is reserved for joins in BI Server.

Example:

SELECT concat_ws('/', 'a', 'b', 'c') --returns "a/b/c"

CONTAINS

Determines if the first string contains the second string, using the specified case and culture comparison. The function has two overloads:

contains(string_to_search, string_tofind, bool_case_sensitive_comparison)
contains(string_to_search, string_tofind, bool_case_sensitive_comparison, bool_culture_comparison)

where

  • string_to_search is the string to search.
  • string_to_find is the string to find.
  • bool_case_sensitive_comparison is a Boolean that, when true, indicates that the comparison is case-sensitive.
  • bool_culture_comparison is a Boolean that, when true, indicates that the comparison will be performed in the resolved culture for the two compared strings; otherwise the comparison will be performed using the invariant culture. The default value is true (culture comparison).

Examples:

SELECT contains('Beverages', 'RA', true) -- returns false
SELECT contains('Beverages', RA, false) -- returns true
SELECT contains('diyarbakır, 'İ', false, false) -- returns false
SELECT contains('diyarbakır' COLLATE tr-tr_ci, 'İ', false, true) -- returns true

ENDSWITH

Determines if the first string ends with the second string, using the specified case and culture comparison. The function has two overloads:

contains(string_to_search, string_suffix, bool_case_sensitive_comparison)
contains(string_to_search, string_suffix, bool_case_sensitive_comparison, bool_culture_comparison)

where

  • string_to_search is the string to search.
  • string_to_suffix is the suffix to search for.
  • bool_case_sensitive_comparison is a Boolean that, when true, indicates that the comparison is case-sensitive.
  • bool_culture_comparison is a Boolean that, when true, indicates that the comparison will be performed in the resolved culture for the two compared strings; otherwise the comparison will be performed using the invariant culture. The default value is true (culture comparison).

Examples:

SELECT endswith('Beverages', 'S', true) -- returns false
SELECT endswith('Beverages', S, false) -- returns true
SELECT endswith('egeli', 'İ', false, false) -- returns false
SELECT endswith('egeli' COLLATE tr-tr_ci, 'İ', false, true) -- returns true

EQUALS

Determines if two strings are equal, using the specified case and culture comparison. The function has two overloads:

equals(string_x, string_y, bool_case_sensitive_comparison)
equals(string_x, string_y, bool_case_sensitive_comparison, bool_culture_comparison)

where

  • string_x and string_y are the two strings to compare.
  • bool_case_sensitive_comparison is a Boolean that, when true, indicates that the comparison is case-sensitive.
  • bool_culture_comparison is a Boolean that, when true, indicates that the comparison will be performed in the resolved culture for the two compared strings; otherwise the comparison will be performed using the invariant culture. The default value is true (culture comparison).

Examples:

SELECT equals('Beverages', 'beverages', true) -- returns false
SELECT equals('Beverages', 'beverages', false) -- returns true
SELECT equals('diyarbakır, 'DİYARBAKIR', false, false) -- returns false
SELECT equals('diyarbakır' COLLATE tr-tr_ci, 'DİYARBAKIR', false, true) -- returns true

INDEXOF

Returns the zero-based index of the first occurrence of a String expression inside another String expression, or -1 if the occurrence was not found. The search is case-sensitive. The function has three overloads:

indexof(string_expression_where_to_search, string_value_expression_to_find)
indexof(string_expression_where_to_search, string_value_expression_to_find, index_where_to_start_search)
indexof(string_expression_where_to_search, string_value_expression_to_find, index_where_to_start_search, number_of_characters_to_examine)

The string_value_expression_to_find parameter can be a single character or a multi-character string.

Examples:

SELECT indexof('Condiments/Confections', 'o') --returns 1
SELECT indexof('Condiments/Confections', 'o', 2) --returns 12
SELECT indexof('Condiments/Confections', 'o', 2, 10) --returns -1
SELECT indexof('Condiments/Confections', 'dime', 2, 10) --returns 3

INDEXOFANY

Returns the zero-based index of the first occurrence within a String expression of any character from another String expression, or -1 if no occurrence was not found. The search is case-sensitive. The function has three overloads:

indexofany(string_expression_where_to_search, set_of_characters_to_search_for)
indexofany(string_expression_where_to_search, set_of_characters_to_search_for, index_where_to_start_search)
indexofany(string_expression_where_to_search, set_of_characters_to_search_for, index_where_to_start_search, number_of_characters_to_examine)

When the set_of_characters_to_search_for parameter is specified as a String, each character in the String will be checked as an individual character.

Examples:

SELECT indexofany('Condiments/Confections', 'no') --returns 1
SELECT indexofany('Condiments/Confections', 'no', 2) --returns 2
SELECT indexofany('Condiments/Confections', 'no', 3, 7) --returns 7

INDEXOFOCCURENCE

Returns the zero-based index of the N-th occurrence of a String expression inside another String expression, or -1 if the occurrence was not found. The search is case-sensitive. The function has three overloads:

indexofoccurrence (string_expression_where_to_search, string_value_expression_to_find, nth_occurrence_to_find)
indexofoccurrence (string_expression_where_to_search, string_value_expression_to_find, nth_occurrence_to_find, index_where_to_start_search)
indexofoccurrence (string_expression_where_to_search, string_value_expression_to_find, nth_occurrence_to_find, index_where_to_start_search, number_of_characters_to_examine)

Examples:

SELECT indexofoccurrence('Condiments/Confections', 'on', 1) --returns 1
SELECT indexofoccurrence('Condiments/Confections', 'on', 2) --returns 12
SELECT indexofoccurrence('Condiments/Confections', 'on', 3) --returns 19
SELECT indexofoccurrence('Condiments/Confections', 'on', 1, 2) --returns 12
SELECT indexofoccurrence('Condiments/Confections', 'on', 1, 2, 10) --returns -1

INVERTCASE

Inverts the case of a String expression.

Example:

SELECT invertcase('aBc') --returns "AbC"

LASTINDEXOF

Returns the zero-based index of the last occurrence of a String expression inside another String expression, or -1 if the occurrence was not found. The search is case-sensitive. The function has three overloads:

indexof(string_expression_where_to_search, string_value_expression_to_find)
indexof(string_expression_where_to_search, string_value_expression_to_find, index_where_to_start_search)
indexof(string_expression_where_to_search, string_value_expression_to_find, index_where_to_start_search, number_of_characters_to_examine)

The string_value_expression_to_find parameter can be a single character or a multi-character string. When the index_where_to_start_search parameter is specified, the search starts at that character backwards for number_of_characters_to_examine characters – or to the beginning of the string if the parameter is not specified.

Examples:

SELECT lastindexof('Condiments/Confections', 'o') --returns 19
SELECT lastindexof('Condiments/Confections', 'o', 18) --returns 12
SELECT lastindexof('Condiments/Confections', 'o', 18, 5) --returns -1

LASTINDEXOFANY

Returns the zero-based index of the last occurrence within a String expression of any character from another String expression, or -1 if no occurrence was not found. The search is case-sensitive. The function has three overloads:

lastindexofany(string_expression_where_to_search, set_of_characters_to_search_for)
lastindexofany(string_expression_where_to_search, set_of_characters_to_search_for, index_where_to_start_search)
lastindexofany(string_expression_where_to_search, set_of_characters_to_search_for, index_where_to_start_search, number_of_characters_to_examine)

When the set_of_characters_to_search_for parameter is specified as a String, each character in the String will be checked as an individual character. When the index_where_to_start_search parameter is specified, the search starts at that character backwards for number_of_characters_to_examine characters – or to the beginning of the string if the parameter is not specified.

Examples:

SELECT lastindexofany('Condiments/Confections', 'no') --returns 20
SELECT lastindexofany('Condiments/Confections', 'no', 18) --returns 13
SELECT lastindexofany('Condiments/Confections', 'no', 10, 10) --returns 7

LEFT

Returns the specified number of characters from the left side of a String Expression.

Example_:

SELECT left('Category', 3) --returns "Cat"

LEN

Returns the length (number of characters) of a String expression.

_Example:

SELECT len('Category') --returns 8

LIKE

Performs a wildcard string search on a String expression. The syntax is:

like(string_expression_where_to_search, pattern_to_search_for, bool_case_sensitive_comparison)

The pattern_to_search_for parameter may contain wildcards. Wildcard characters include:

  • ( ? - question mark): Matches any single character.
  • ( * - asterisk): Matches any character zero or more times.
  • ( # - pound): Matches any single digit (0-9).
  • [charlist]: Matches any single character in charlist
  • [!charlist]: Matches any single character that is not in charlist

To match a literal wildcard, enclose it in square brackets, i.e.: [*].

Examples:

SELECT like('Category', 'cat*', false) --returns true
SELECT like('Category', 'cat*', true) --returns false
SELECT like('Category', 'c?t*', false), like('Cotton', 'c?t*', false) --both return true
SELECT like('12', '1#', false) --returns true for all two digit numbers starting with 1
SELECT like('cat', 'c[ao]t', false), like('cot', 'c[ao]t', false), like('cut', 'c[ao]t', false) --matches "cat", "cot" but not "cut"
SELECT like('cat', 'c[!a]t', false) --matches any three-letter word starting with "c" and ending with "t" but "cat"
SELECT like('hello?', '*[?]', false) --matches any word ending in question mark
This function is available for compatibility with the Expression Engine however, in BI Server, it is recommended to use the LIKE operator.

LOWER

Makes a String expression lowercase. If the COLLATE keyword is not specified, the conversion is performed using the casing rules of the culture of the Data Model’s collation. This function produces the same result as the TOLOWER function.

Examples:

SELECT lower('DİYARBAKIR') --returns "diyarbakir" in the en-us culture
SELECT lower('DİYARBAKIR' COLLATE tr-tr_ci) --returns "diyarbakır"

NCHAR

Returns the Unicode character with the specified integer code, as defined by the Unicode standard. This function is similar to the ASCIITOWCHAR function, but it only operates on a single character.

Example:

SELECT nchar(8721) --returns ∑

PATINDEX

Returns the starting position of the first occurrence of a pattern in a specified String expression, or 0 if the pattern is not found. If the COLLATE keyword is not specified, the culture of the data model’s collation will be used for the comparisons. The index result of this function is 1-based, meaning that a match at the first character will return 1 instead of 0, unlike other functions like INDEXOF. The syntax is:

patindex(pattern_to_search_for, string_expression_where_to_search)

The pattern_to_search_for parameter works the same way as the LIKE operator and accepts the same patterns, with the difference that the % character must come before and follow the pattern (except when searching for first or last characters).

Example:

SELECT patindex('%t_g%', 'Category') --returns 3

The following example uses the ^ character to match the first character that is not a letter, digit, or space.

Example:

SELECT patindex('%[^ 0-9A-z]%', 'Welcome!') --returns 8

The following examples show the usage of PATINDEX with the COLLATE keyword.

Examples:

SELECT patindex('%I%', 'kır' COLLATE en-us_ci) --returns 0
SELECT patindex('%I%', 'kır' COLLATE tr-tr_ci) --returns 2

RGMATCH

Returns whether the regular expression matches the input expression. This function has three overloads:

rgmatch(string_expression_where_to_search, regex_pattern_to_search_for)
rgmatch(string_expression_where_to_search, regex_pattern_to_search_for, index_where_to_start_search)
rgmatch(string_expression_where_to_search, regex_pattern_to_search_for, index_where_to_start_search, regex_options)

When the regex_options parameter is specified, it must be a combination of the following values:

  • rg_culture_inv(): Makes comparisons use the invariant culture.
  • rg_ignore_case(): Specifies case-insensitive matching.
  • rg_ignore_pattern_ws(): Eliminates unescaped white space from the pattern.

The following examples use a regular expression to match ZIP codes with 5 digits or 5+4 digits.

Examples:

SELECT rgmatch('02035', '^\\d{5}(-\\d{4})?$') --returns true
SELECT rgmatch('02035-0000', '^\\d{5}(-\\d{4})?$') --returns true

The following examples show the usage of the start index.

Examples:

SELECT rgmatch('Condiments', 'Con.*', 0) --returns true
SELECT rgmatch('Condiments', 'Con.*', 1) --returns false

The following examples show the usage of the options.

Examples:

SELECT rgmatch('Condiments', 'con.*', 0) --returns false SELECT rgmatch('Condiments', 'con.*', 0) --returns false SELECT rgmatch('Condiments', 'con.*', 0, rg_ignore_case()) --returns true SELECT rgmatch('Condiments', ' con.*', 0, rg_ignore_case() + rg_ignore_pattern_ws()) --returns true

RGREPLACE

Replaces all the instances of the string that match a regular expression pattern in a String expression with the specified replacement string. This function has two overloads:

rgreplace(string_expression_where_to_search, regex_pattern_to_search_for, string_expression_replacement_string)
rgreplace(string_expression_where_to_search, regex_pattern_to_search_for, string_expression_replacement_string, regex_options)

The following examples show usage of the RGREPLACE function.

Examples:

SELECT rgreplace('--abc--adc--', 'a[a-z]c', 'xyz') --returns "--xyz--xyz---"
SELECT rgreplace('--abc--aDc--', 'a[a-z]c', 'xyz') --returns "--xyz--aDc---"
SELECT rgreplace('--abc--aDc--', 'a[a-z]c', 'xyz', rg_ignore_case()) --returns "--xyz--xyz---"

REPLACE

Replaces all the instances of the string that match the specified search String expression in a String expression with the replacement String expression. The function has the following syntax:

replace(string_expression_where_to_search, string_expression_to_search_for, string_expression_replacement_string)

Example:

SELECT replace('--abc--abc--adc--', 'abc', 'xyz') --returns "--xyz--xyz--adc--"

REVERSE

Reverses a string.

Example:

SELECT reverse('tab') --returns "bat"

RIGHT

Returns the specified number of characters from the right side of a String Expression.

Example:

SELECT right('Category', 3) --returns "ory"

STARTSWITH

Determines if the first String starts with the second String, using the specified case and culture comparison. The function has two overloads:

contains(string_to_search, string_prefix, bool_case_sensitive_comparison)
contains(string_to_search, string_prefix, bool_case_sensitive_comparison, bool_culture_comparison)

where

  • string_to_search: The string to search.
  • string_to_prefix The prefix to be searched for.
  • bool_case_sensitive_comparison: A Boolean that, when true, indicates that the comparison is case-sensitive.
  • bool_culture_comparison: A Boolean that, when true, indicates that the comparison will be performed in the resolved culture for the two compared strings; otherwise the comparison will be performed using the invariant culture. The default value is true (culture comparison).

Examples:

SELECT startswith('Beverages', 'b', true) -- returns false
SELECT startswith('Beverages', b, false) -- returns true
SELECT startswith('iade', 'İ', false, false) -- returns false
SELECT startswith('iade' COLLATE tr-tr_ci, 'İ', false, true) -- returns true

SUBSTRING

Extracts a portion of a String expression. The function has two overloads:

substring(string_expression_input, index_where_to_start_extraction)
substring(string_expression_input, index_where_to_start_extraction, number_of_characters_to_extract)

When the number_of_characters_to_extract parameter is not specified, the substring starts from index_where_to_start_extraction and ends at the end of string_expression_input. If index_where_to_start_extraction goes over the length of string_expression_input, an empty string is returned.

Examples:

SELECT substring('hello world', 6) --returns "world"
SELECT substring('hello world', 3, 5) --returns "lo wo"
SELECT substring('hello world', 12) --returns an empty string

TOLOWER

Makes a String expression lowercase. If the COLLATE keyword is not specified, the conversion is performed using the casing rules of the culture of the Data Model’s collation. This function produces the same result as the LOWER function.

Examples:

SELECT tolower('DİYARBAKIR') --returns "diyarbakir" in the en-us culture
SELECT tolower('DİYARBAKIR' COLLATE tr-tr_ci) --returns "diyarbakır"

TOUPPER

Makes a String expression uppercase. If the COLLATE keyword is not specified, the conversion is performed using the casing rules of the culture of the Data Model’s collation. This function produces the same result as the UPPER function.

Examples:

SELECT toupper('diyarbakır') --returns "DIYARBAKIR"
SELECT toupper('diyarbakır' COLLATE tr-tr_ci) --returns "DİYARBAKIR"

TRIM

Removes all leading and trailing occurrences of the specified set of characters from an input String expression. The function has two overloads:

trim(string_expression_to_trim)
trim(string_expression_to_trim, characters_to_trim)

If the characters_to_trim parameter is not specified, all leading and trailing spaces will be removed from string_expression_to_trim. Otherwise, each character specified in characters_to_trim will be removed from string_expression_to_trim if it is either leading or trailing.

Examples:

SELECT 'a' + trim(' b ') + 'c' --returns "abc"
SELECT 'a' + trim('#b$', '#$') + 'c' --returns "abc"

TRIMLEFT

Removes all leading occurrences of the specified set of characters from an input String expression. The function has two overloads:

trimleft(string_expression_to_trim)
trimleft(string_expression_to_trim, characters_to_trim)

If the characters_to_trim parameter is not specified, all leading spaces will be removed from string_expression_to_trim. Otherwise, each character specified in characters_to_trim will be removed from string_expression_to_trim if it is leading.

Examples:

SELECT 'a' + trimleft(' b ') + 'c' --returns "ab c"
SELECT 'a' + trimleft('#b$', '#$') + 'c' --returns "ab$c"

TRIMRIGHT

Removes all trailing occurrences of the specified set of characters from an input String expression. The function has two overloads:

trimright(string_expression_to_trim)
trimright(string_expression_to_trim, characters_to_trim)

If the characters_to_trim parameter is not specified, all trailing spaces will be removed from string_expression_to_trim. Otherwise, each character specified in characters_to_trim will be removed from string_expression_to_trim if it is trailing.

Examples:

SELECT 'a' + trimright(' b ') + 'c' --returns "a bc"
SELECT 'a' + trimright('#b$', '#$') + 'c' --returns "a#bc"

UPPER

Makes a String expression uppercase. If the COLLATE keyword is not specified, the conversion is performed using the casing rules of the culture of the Data Model’s collation. This function produces the same result as the TOUPPER function.

Examples:

SELECT upper('diyarbakır') --returns "DIYARBAKIR"
SELECT upper('diyarbakır' COLLATE tr-tr_ci) --returns "DİYARBAKIR"

URLDECODE

Encodes a URL String expression.

Example:

SELECT urlencode('a parameter value') --returns "a+parameter+value"

URLENCODE

Decodes a URL String expression.

Example:

SELECT urldecode('a+parameter+value') --returns "a parameter value"

WCHARTOASCII

Converts a String expression of one or more characters into an Int64 value representing the corresponding Unicode values. Each character in the input String expression represents two bytes in the resulting Int64 value. As the output of this function is an Int64 value, the input expression is limited to 4 characters.

Examples:

SELECT wchartoascii('∑') --returns 8721
SELECT wchartoascii('東京') --returns 1735478956