This page lists the string functions available in the GSQL query language
ascii( str )
Returns the ASCII (numeric) position of the first character in a string. If the argument is an empty string, returns 0.
INT
chr( n )
Converts an integer to a character according to its ASCII position. If the input value n
is greater than 255, returns the character at the position of the modulo of n / 256
.
STRING
difference(str1, str2)
Compares the Soundex codes of two strings and returns an integer. The return value ranges from 0 to 4, and it indicates the similarity between the input strings' phonetic representation values.
0 indicates weak similarity, and 4 indicates strong similarity with identical phonetic representation values.
INT
find_in_set(str, str_list)
Returns the position of a string within a list of strings separated by commas.
If string
is not found in string_list
, this function returns -1.
NOTE:
find_in_set
does not ignore whitespace after the comma. For example,find_in_set("a", "b a, a") = 0
, indicating that “a” is not an element in the list. This happens because the second and third elements of the list are space+a, rather than a.
INT
gsql_uuid_v4()
Generates and returns a version-4 universally unique identifier(UUID).
STRING
None.
insert(str1, position[, number], str2)
Inserts a string within a string at the specified position and for a certain number of characters, and replaces a specified number of characters starting from the insertion position. The starting index is 0.
STRING
instr (str, substr [, position, occurence])
Searches a string (str
) for a substring (substr
) and returns the location of the substring in the string. If a substring that is equal to substr
is found, then the function returns an integer indicating the position of the first character of this substring. If no such substring is found, then the function returns -1.
INT
left(str, number_of_chars)
Extracts a number of characters from a string starting from left.
STRING
length(str)
Returns the length of the input string.
INT
ltrim( str[, set] )
Removes all occurrences of the characters contained in a set from a string from the left side.
The function begins scanning the string from its first character and removes all characters that appear in set
until reaching a character not in set
and then returns the result.
STRING
lower(str)
Returns the input string with all letters in lowercase.
STRING
lpad(str, padded_length [, pad_str] )
Pads the left side of a string with another pad string. If the pad string (pad_str
) is omitted, it will pad with white space. If the parameter length is smaller than the original string, it will truncate the string from the right side.
STRING
replace(str, str_to_replace [, replacement_str])
Replaces a sequence of characters in a string with another set of characters.
STRING
right(str, number_of_chars)
Extracts a number of characters from a string starting from the right.
STRING
rpad(str, padded_length [, pad_str] )
Pads the right side of a string (str
) with another pad string. If the pad string (pad_str
) is omitted, it will pad with white space. If the parameter length is smaller than the original string, it will truncate the string from the right side.
STRING
rtrim( str [,set] )
Removes all occurrences of the characters contained in a set from a string from the right side.
The function begins scanning the string from its last character and removes all characters that appear in set
until reaching a character not in set
and then returns the result.
STRING
soundex( str )
Returns a character string containing the Soundex code of str
. This function lets you compare words that are spelled differently, but sound alike in English.
Soundex is a phonetic algorithm defined in The Art of Computer Programming, Volume 3: Sorting and Searching, by Donald E. Knuth, as follows:
Retain the first letter of the string and remove all other occurrences of the following letters: a, e, h, i, o, u, w, y.
Assign numbers to the remaining letters (after the first) as follows:
If two or more letters with the same number were adjacent in the original name (before step 1), or adjacent except for any intervening h and w, then retain the first letter and omit the rest of all the adjacent letters with the same number.
Return the first four bytes padded with 0.
STRING
space( n )
Returns a string that contains the specified number of space characters
STRING
substr(str, start [, length])
Returns the substring indicated by the start point and length. If the parameter length is omitted, then it will extend to the end.
STRING
translate( str_origin, characters, translations )
Returns the string from the first argument after the characters specified in the second argument are translated into the characters specified at the same index in the third argument.
NOTE: The function will return an error if
characters
andtranslations
have different lengths.
STRING
trim( [ [ LEADING | TRAILING | BOTH ] [removal_char FROM] ] str )
Trims characters from the leading and/or trailing ends of a string.
By using one of the keywords LEADING
, TRAILING
, or BOTH
, the user can specify that characters are to be removed from the left end, right end, or both ends of the string, respectively. If none of these keywords is used, the function will remove from both ends.
STRING
upper(str)
Returns the input string with all letters in uppercase.
STRING
Parameter
Description
Data type
str
A string value
STRING
Parameter
Description
Data type
n
An integer value
INT
Parameter
Description
Data type
str1
A string value
STRING
str2
A string value
STRING
Parameter
Description
Data type
str
A string value
STRING
str_list
A string representation of a list of strings.
STRING
Parameter
Description
Data type
str1
The string to insert another string into
STRING
position
The index of the starting position to insert the string
INT
number
Optional. The number of characters from the original string that will be replaced. If the argument is left off, it defaults to 0.
STRING
str2
The string to be inserted
STRING
Parameter
Description
Data type
str
The string to search
STRING
substr
The string to search for in str
STRING
position
Optional. The position is a nonzero integer indicating the character of str
from where the search begins. If omitted, it defaults to 0. The first position in the string is 0. If position
is negative, then the function counts backward from the end of str
and then searches backward from the resulting position.
STRING
occurrence
Optional, The occurrence is an integer indicating which occurrence of substr
in str
the function should search for.
STRING
Parameter
Description
Data type
str
A string value
STRING
number_of_chars
The number of characters to extract
INT
Parameter
Description
Data type
str
The string whose length to evaluate
STRING
Parameter
Description
Data type
str
A string value
STRING
set
Optional. A string of characters. The distinct characters from the string form the set. If not specified, it defaults to a single space.
STRING
Parameter
Description
Data type
str
The string to convert to lowercase
STRING
Parameter
Description
Data type
str
The string to pad characters to
STRING
padded_length
The number of characters to return. If the padded_length
is smaller than the original string, the lpad
function will truncate the string to the size of padded_length
.
INT
pad_str
Optional. This is the string that will be padded to the left-hand side of str
. If this parameter is omitted, the lpad
function will pad spaces to the left-side of str
.
STRING
Parameter
Description
Data type
str
The original string whose substrings are to be replaced
STRING
str_to_replace
The string that will be searched for and replaced in str
STRING
replacement_str
Optional. The string that will replace str_to_replace
. If omitted, replace()
removes all occurrences of string_to_replace, and returns the resulting string.
STRING
Parameter
Description
Data type
str
A string value
STRING
number_of_chars
The number of characters to extract
INT
Parameter
Description
Data type
str
The string to pad characters to
STRING
padded_length
The number of characters to return. If the padded_length
is smaller than the original string, the lpad
function will truncate the string to the size of padded_length
.
INT
pad_str
Optional. This is the string that will be padded to the right-hand side of str
. If this parameter is omitted, the lpad
function will pad spaces to the right-side of str
.
STRING
Parameter
Description
Data type
str
A string value
STRING
set
Optional. A string of characters. The distinct characters from the string form the set. If not specified, it defaults to a single space.
STRING
Parameter
Description
Data type
str
A string value
STRING
Parameter
Description
Data type
n
An integer value
INT
Parameter
Description
Data type
str
The string to extract substring from
STRING
start
The position that indicates the start of the substring
INT
length
Optional. The length of the substring. If omitted, the substring will be
INT
Parameter
Description
Data type
str_origin
A string value
STRING
characters
A string of characters
STRING
translations
A string of characters
STRING
Parameter
Description
Data type
removal_char
Optional. The character to remove. If removal_char
is not specified, the function will remove whitespaces, including spaces, tabs, and newlines. If removal_char
is specified, the user must also write the keyword FROM
between removal_char
and str
.
STRING
str
A string value.
STRING
Parameter
Description
Data type
str
The string to convert to uppercase
STRING