String Functions
This page lists the string functions available in the GSQL query language. Examples of string functions are converting to uppercase or lowercase, padding or trimming characters, extracting substrings and comparing phonetic values for English words.
For type conversion functions, such as converting a STRING
to an int
or DATETIME
, please see the Type Conversion Functions page.
chr()
difference()
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.
find_in_set()
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.
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 .
|
gsql_uuid_v4()
Description
Generates and returns a version-4 universally unique identifier (UUID).
insert()
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.
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 |
|
instr()
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.
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 |
|
ltrim()
lpad()
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.
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 |
|
replace()
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 |
|
rpad()
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.
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 |
|
rtrim()
soundex()
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:
b, f, p, v = 1 c, g, j, k, q, s, x, z = 2 d, t = 3 l = 4 m, n = 5 r = 6
-
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.
substr()
Description
Returns the substring indicated by the start point and length. If the parameter length is omitted, then the returned substring will extend to the end of the given input string.
translate()
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.
The function will return an error if characters
and translations
have different lengths.
trim()
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. BOTH
is the default and will be used if no keywords are specified.
Parameters
Parameter | Description | Data type |
---|---|---|
|
Optional. The character to remove. If |
|
|
A string value. |
|