String Functions
This page lists the string functions available in the GSQL query language
ascii()
Syntax
ascii( str )
Description
Returns the ASCII (numeric) position of the first character in a string. If the argument is an empty string, returns 0.
Return type
INT
Parameters
Parameter | Description | Data type |
| A string value |
|
Example
chr()
Syntax
chr( n )
Description
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
.
Return type
STRING
Parameters
Parameter | Description | Data type |
| An integer value |
|
Examples
difference()
Syntax
difference(str1, str2)
Description
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.
Return type
INT
Parameters
Parameter | Description | Data type |
| A string value |
|
| A string value |
|
Examples
find_in_set()
Syntax
find_in_set(str, str_list)
Description
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.
Return type
INT
Parameters
Parameter | Description | Data type |
| A string value |
|
| A string representation of a list of strings. |
|
Example
gsql_uuid_v4()
Syntax
gsql_uuid_v4()
Description
Generates and returns a version-4 universally unique identifier(UUID).
Return type
STRING
Parameters
None.
insert()
Syntax
insert(str1, position[, number], str2)
Description
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.
Return type
STRING
Parameters
Parameter | Description | Data type |
| The string to insert another string into |
|
| The index of the starting position to insert the string |
|
| Optional. The number of characters from the original string that will be replaced. If the argument is left off, it defaults to 0. |
|
| The string to be inserted |
|
Examples
instr()
Syntax
instr (str, substr [, position, occurence])
Description
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.
Return type
INT
Parameters
Parameter | Description | Data type |
| The string to search |
|
| The string to search for in |
|
| Optional. The position is a nonzero integer indicating the character of |
|
| Optional, The occurrence is an integer indicating which occurrence of |
|
Example
left()
Syntax
left(str, number_of_chars)
Description
Extracts a number of characters from a string starting from left.
Return type
STRING
Parameters
Parameter | Description | Data type |
| A string value |
|
| The number of characters to extract |
|
length()
Syntax
length(str)
Description
Returns the length of the input string.
Return type
INT
Parameters
Parameter | Description | Data type |
| The string whose length to evaluate |
|
Example
ltrim()
Syntax
ltrim( str[, set] )
Description
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.
Return type
STRING
Parameters
Parameter | Description | Data type |
| A string value |
|
| Optional. A string of characters. The distinct characters from the string form the set. If not specified, it defaults to a single space. |
|
lower()
Syntax
lower(str)
Description
Returns the input string with all letters in lowercase.
Return type
STRING
Parameters
Parameter | Description | Data type |
| The string to convert to lowercase |
|
Example
lpad()
Syntax
lpad(str, padded_length [, pad_str] )
Description
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.
Return type
STRING
Parameters
Parameter | Description | Data type |
| The string to pad characters to |
|
| The number of characters to return. If the |
|
| Optional. This is the string that will be padded to the left-hand side of |
|
Example
replace()
Syntax
replace(str, str_to_replace [, replacement_str])
Description
Replaces a sequence of characters in a string with another set of characters.
Return type
STRING
Parameters
Parameter | Description | Data type |
| The original string whose substrings are to be replaced |
|
| The string that will be searched for and replaced in |
|
| Optional. The string that will replace |
|
Examples
right()
Syntax
right(str, number_of_chars)
Description
Extracts a number of characters from a string starting from the right.
Return type
STRING
Parameters
Parameter | Description | Data type |
| A string value |
|
| The number of characters to extract |
|
rpad()
Syntax
rpad(str, padded_length [, pad_str] )
Description
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.
Return type
STRING
Parameters
Parameter | Description | Data type |
| The string to pad characters to |
|
| The number of characters to return. If the |
|
| Optional. This is the string that will be padded to the right-hand side of |
|
Example
rtrim()
Syntax
rtrim( str [,set] )
Description
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.
Return type
STRING
Parameters
Parameter | Description | Data type |
| A string value |
|
| Optional. A string of characters. The distinct characters from the string form the set. If not specified, it defaults to a single space. |
|
soundex()
Syntax
soundex( str )
Description
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.
Return type
STRING
Parameters
Parameter | Description | Data type |
| A string value |
|
Examples
space()
Syntax
space( n )
Description
Returns a string that contains the specified number of space characters
Return type
STRING
Parameters
Parameter | Description | Data type |
| An integer value |
|
Examples
substr()
Syntax
substr(str, start [, length])
Description
Returns the substring indicated by the start point and length. If the parameter length is omitted, then it will extend to the end.
Return type
STRING
Parameters
Parameter | Description | Data type |
| The string to extract substring from |
|
| The position that indicates the start of the substring |
|
| Optional. The length of the substring. If omitted, the substring will be |
|
Example
translate()
Syntax
translate( str_origin, characters, translations )
Description
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.
Return type
STRING
Parameters
Parameter | Description | Data type |
| A string value |
|
| A string of characters |
|
| A string of characters |
|
Examples
trim()
Syntax
trim( [ [ LEADING | TRAILING | BOTH ] [removal_char FROM] ] str )
Description
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.
Return type
STRING
Parameters
Parameter | Description | Data type |
| Optional. The character to remove. If |
|
| A string value. |
|
Example
upper()
Syntax
upper(str)
Description
Returns the input string with all letters in uppercase.
Return type
STRING
Parameters
Parameter | Description | Data type |
| The string to convert to uppercase |
|
Example
Last updated