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.

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

Parameter	Description	Data type
`str`	A string value	`STRING`

str

A string value

STRING

Example

ascii("") => 0
ascii("A") => 65

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

Parameter	Description	Data type
`n`	An integer value	`INT`

n

An integer value

INT

Examples

chr(65) => 'A'
chr(322) => 'B'

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

Parameter	Description	Data type
`str1`	A string value	`STRING`
`str2`	A string value	`STRING`

str1

A string value

STRING

str2

A string value

STRING

Examples

difference("Johnson", "Jonson") => 4
difference("Adams", "Addamms") => 4
difference("Mary", "Bob") => 2

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.

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

Parameter	Description	Data type
`str`	A string value	`STRING`
`str_list`	A string representation of a list of strings.	`STRING`

str

A string value

STRING

str_list

A string representation of a list of strings.

STRING

Example

find_in_set("a","") => 0
find_in_set("a","b,c,d") => 0
find_in_set("a","b,a,d") => 2

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

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`

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

Examples

insert("tigergraph.com", 0, 10, "Example") => "Example.com”
insert("tigergraph.com", 0, 2, "Example") => "Examplegergraph.com”
insert("tigergraph.com", 2, 20, "Example") => ”tiExample”
insert("Complete blank.", 9, "every ") => "Complete every blank."

instr()

Syntax

instr (str, substr [, position, occurrence])

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

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`

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

Example

instr("This is the thing", "Th") -> 0;
instr("This is the thing", "is", 3) -> 5;

left()

Syntax

left(str, number_of_chars)

Description

Extracts a number of characters from a string starting from position 0 (capturing left to right).

Return type

STRING

Parameters

Parameter Description Data type

Parameter	Description	Data type
`str`	A string value	`STRING`
`number_of_chars`	The number of characters to extract	`INT`

str

A string value

STRING

number_of_chars

The number of characters to extract

INT

length()

Syntax

length(str)

Description

Returns the length of the input string.

Return type

INT

Parameters

Parameter Description Data type

Parameter	Description	Data type
`str`	The string whose length to evaluate	`STRING`

str

The string whose length to evaluate

STRING

Example

length("hello world") -> 11
length("") -> 0

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, removing all characters that appear in set until reaching a character not in set, then returning the result.

Return type

STRING

Parameters

Parameter Description Data type

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`

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

lower()

Syntax

lower(str)

Description

Returns the input string with all letters in lowercase.

Return type

STRING

Parameters

Parameter Description Data type

Parameter	Description	Data type
`str`	The string to convert to lowercase	`STRING`

str

The string to convert to lowercase

STRING

Example

lower("GSQL") -> "gsql"

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

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`

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

Example

 lpad("PQR", 5) -> "  PQR"
 lpad("PQR", 2) -> "PQ"
 lpad("PQR", 10, "ABC") -> "ABCABCAPQR"

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

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`

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

Examples

 replace("SSQLL", "S", "G") -> "GGQLL"
 replace("SSQLL", "SQL", "Q") -> "SQL"
 replace("SSQLL", "L") -> "SSQ"

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

Parameter	Description	Data type
`str`	A string value	`STRING`
`number_of_chars`	The number of characters to extract	`INT`

str

A string value

STRING

number_of_chars

The number of characters to extract

INT

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

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`

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

Example

rpad("PQR", 5) -> "PQF  "
lpad("PQR", 2) -> "PQ"
lpad("PQR", 10, "ABC") -> "ABCABCAPQR"

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

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`

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

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:

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.

Return type

STRING

Parameters

Parameter Description Data type

Parameter	Description	Data type
`str`	A string value	`STRING`

str

A string value

STRING

Examples

soundex("Ashcraft") => "A261"
soundex("Burroughs") => "B620"
soundex("Burrows") => "B620"

space()

Syntax

space( n )

Description

Returns a string that contains the specified number of space characters

Return type

STRING

Parameters

Parameter Description Data type

Parameter	Description	Data type
`n`	An integer value	`INT`

n

An integer value

INT

Examples

space(0) = ””
space(1) = ” ”
space(5) = ”     ”

substr()

Syntax

substr(str, start [, length])

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.

Return type

STRING

Parameters

Parameter Description Data type

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 extend from `start` to the end of `str`.	`INT`

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 extend from start to the end of str.

INT

Example

substr("ABCDE", 2) -> "CDE"
substr("ABCDE", 2, 2) -> "CD"
substr("ABCDE", -2, 1) -> "D"

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.

The function will return an error if characters and translations have different lengths.

Return type

STRING

Parameters

Parameter Description Data type

Parameter	Description	Data type
`str_origin`	A string value	`STRING`
`characters`	A string of characters	`STRING`
`translations`	A string of characters	`STRING`

str_origin

A string value

STRING

characters

A string of characters

STRING

translations

A string of characters

STRING

Examples

translate(”Hello world”, "", "") => ”Hello world”
translate(”Hello world”, "o", "U") => ”HellU wUrld”
translate(”Hello world”, "lo", "aU") => ”HeaaU wUrad”
translate(””, "lo", "aU") => ””

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. BOTH is the default and will be used if no keywords are specified.

Return type

STRING

Parameters

Parameter Description Data type

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`

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

Example

trim("  Abc   ") => "Abc"
trim( LEADING " a A   ") => "a A   "
trim( TRAILING "a" FROM "aa ABC aaa") => "aa ABC "

upper()

Syntax

upper(str)

Description

Returns the input string with all letters in uppercase.

Return type

STRING

Parameters

Parameter Description Data type

Parameter	Description	Data type
`str`	The string to convert to uppercase	`STRING`

str

The string to convert to uppercase

STRING

Example

upper("gsql") -> "GSQL"