Built-in string functions¶

This topic describes the built-in string functions supported by NebulaGraph.

Precautions¶

A string type is used to store a sequence of characters (text). The literal constant is a sequence of characters of any length surrounded by double or single quotes.

Like SQL, the position index of nGQL starts from 1, while in C language it starts from 0.

strcasecmp()¶

strcasecmp() compares string a and b without case sensitivity.

Syntax: strcasecmp(<string_a>,<string_b>)

string_a, string_b: Strings to compare.

Result type: Int

When string_a = string_b, the return value is 0. When string_a > string_b, the return value is greater than 0. When string_a < string_b, the return value is less than 0.

Example:

nebula> RETURN strcasecmp("a","aa");
+----------------------+
| strcasecmp("a","aa") |
+----------------------+
| -97                  |
+----------------------+

lower() and toLower()¶

lower() and toLower() can both returns the argument in lowercase.

Syntax: lower(<string>), toLower(<string>)

string: A specified string.

Result type: String

Example:

nebula> RETURN lower("Basketball_Player");
+----------------------------+
| lower("Basketball_Player") |
+----------------------------+
| "basketball_player"        |
+----------------------------+

upper() and toUpper()¶

upper() and toUpper() can both returns the argument in uppercase.

Syntax: upper(<string>), toUpper(<string>)

string: A specified string.

Result type: String

Example:

nebula> RETURN upper("Basketball_Player");
+----------------------------+
| upper("Basketball_Player") |
+----------------------------+
| "BASKETBALL_PLAYER"        |
+----------------------------+

length()¶

length() returns the length of the given string in bytes.

Syntax: length({<string>|<path>})

string: A specified string.
path: A specified path represented by a variable.
Result type: Int

Example:

nebula> RETURN length("basketball");
+----------------------+
| length("basketball") |
+----------------------+
| 10                   |
+----------------------+

nebula> MATCH p=(v:player{name:"Tim Duncan"})-->(v2) return length(p);
+-----------+
| length(p) |
+-----------+
| 1         |
| 1         |
| 1         |
+-----------+

trim()¶

trim() removes the spaces at the leading and trailing of the string.

Syntax: trim(<string>)

string: A specified string.

Result type: String

Example:

nebula> RETURN trim(" basketball player ");
+-----------------------------+
| trim(" basketball player ") |
+-----------------------------+
| "basketball player"         |
+-----------------------------+

ltrim()¶

ltrim() removes the spaces at the leading of the string.

Syntax: ltrim(<string>)

string: A specified string.

Result type: String

Example:

nebula> RETURN ltrim(" basketball player ");
+------------------------------+
| ltrim(" basketball player ") |
+------------------------------+
| "basketball player "         |
+------------------------------+

rtrim()¶

rtrim() removes the spaces at the trailing of the string.

Syntax: rtrim(<string>)

string: A specified string.

Result type: String

Example:

nebula> RETURN rtrim(" basketball player ");
+------------------------------+
| rtrim(" basketball player ") |
+------------------------------+
| " basketball player"         |
+------------------------------+

left()¶

left() returns a substring consisting of several characters from the leading of a string.

Syntax: left(<string>,<count>)

string: A specified string.

count: The number of characters from the leading of the string. If the string is shorter than count, the system returns the string itself.

Result type: String

Example:

nebula> RETURN left("basketball_player",6);
+-----------------------------+
| left("basketball_player",6) |
+-----------------------------+
| "basket"                    |
+-----------------------------+

right()¶

right() returns a substring consisting of several characters from the trailing of a string.

Syntax: right(<string>,<count>)

string: A specified string.

count: The number of characters from the trailing of the string. If the string is shorter than count, the system returns the string itself.

Result type: String

Example:

nebula> RETURN right("basketball_player",6);
+------------------------------+
| right("basketball_player",6) |
+------------------------------+
| "player"                     |
+------------------------------+

lpad()¶

lpad() pads a specified string from the left-side to the specified length and returns the result string.

Syntax: lpad(<string>,<count>,<letters>)

string: A specified string.

count: The length of the string after it has been left-padded. If the length is less than that of string, only the length of string characters from front to back will be returned.

letters: A string to be padding from the leading.

Result type: String

Example:

nebula> RETURN lpad("abcd",10,"b");
+---------------------+
| lpad("abcd",10,"b") |
+---------------------+
| "bbbbbbabcd"        |
+---------------------+
nebula> RETURN lpad("abcd",3,"b");
+--------------------+
| lpad("abcd",3,"b") |
+--------------------+
| "abc"              |
+--------------------+

rpad()¶

rpad() pads a specified string from the right-side to the specified length and returns the result string.

Syntax: rpad(<string>,<count>,<letters>)

string: A specified string.

count: The length of the string after it has been right-padded. If the length is less than that of string, only the length of string characters from front to back will be returned.

letters: A string to be padding from the trailing.

Result type: String

Example:

nebula> RETURN rpad("abcd",10,"b");
+---------------------+
| rpad("abcd",10,"b") |
+---------------------+
| "abcdbbbbbb"        |
+---------------------+
nebula> RETURN rpad("abcd",3,"b");
+--------------------+
| rpad("abcd",3,"b") |
+--------------------+
| "abc"              |
+--------------------+

substr() and substring()¶

substr() and substring() return a substring extracting count characters starting from the specified position pos of a specified string.

Syntax: substr(<string>,<pos>,<count>), substring(<string>,<pos>,<count>)

string: A specified string.

pos: The position of starting extract (character index). Data type is int.

count: The number of characters extracted from the start position onwards.

Result type: String

Explanations for the return of `substr()` and `substring()`¶

If pos is 0, it extracts from the specified string leading (including the first character).

If pos is greater than the maximum string index, an empty string is returned.

If pos is a negative number, BAD_DATA is returned.

If count is omitted, the function returns the substring starting at the position given by pos and extending to the end of the string.

If count is 0, an empty string is returned.

Using NULL as any of the argument of substr() will cause an issue.

OpenCypher compatibility

In openCypher, if a is null, null is returned.

Example:

nebula> RETURN substr("abcdefg",2,4);
+-----------------------+
| substr("abcdefg",2,4) |
+-----------------------+
| "cdef"                |
+-----------------------+
nebula> RETURN substr("abcdefg",0,4);
+-----------------------+
| substr("abcdefg",0,4) |
+-----------------------+
| "abcd"                |
+-----------------------+
nebula> RETURN substr("abcdefg",2);
+---------------------+
| substr("abcdefg",2) |
+---------------------+
| "cdefg"             |
+---------------------+

reverse()¶

reverse() returns a string in reverse order.

Syntax: reverse(<string>)

string: A specified string.

Result type: String

Example:

nebula> RETURN reverse("abcdefg");
+--------------------+
| reverse("abcdefg") |
+--------------------+
| "gfedcba"          |
+--------------------+

replace()¶

replace() replaces string a in a specified string with string b.

Syntax: replace(<string>,<substr_a>,<string_b>)

string: A specified string.

substr_a: String a.

string_b: String b.

Result type: String

Example:

nebula> RETURN replace("abcdefg","cd","AAAAA");
+---------------------------------+
| replace("abcdefg","cd","AAAAA") |
+---------------------------------+
| "abAAAAAefg"                    |
+---------------------------------+

split()¶

split() splits a specified string at string b and returns a list of strings.

Syntax: split(<string>,<substr>)

string: A specified string.

substr: String b.

Result type: List

Example:

nebula> RETURN split("basketballplayer","a");
+-------------------------------+
| split("basketballplayer","a") |
+-------------------------------+
| ["b", "sketb", "llpl", "yer"] |
+-------------------------------+

concat()¶

concat() returns strings concatenated by all strings.

Syntax: concat(<string1>,<string2>,...)

The function requires at least two or more strings. If there is only one string, the string itself is returned.

If any one of the strings is NULL, NULL is returned.

Result type: String

Example:

//This example concatenates 1, 2, and 3.
nebula> RETURN concat("1","2","3") AS r;
+-------+
| r     |
+-------+
| "123" |
+-------+

//In this example, one of the string is NULL.
nebula> RETURN concat("1","2",NULL) AS r;
+----------+
| r        |
+----------+
| __NULL__ |
+----------+

nebula> GO FROM "player100" over follow \
        YIELD concat(src(edge), properties($^).age, properties($$).name, properties(edge).degree) AS A;
+------------------------------+
| A                            |
+------------------------------+
| "player10042Tony Parker95"   |
| "player10042Manu Ginobili95" |
+------------------------------+

concat_ws()¶

concat_ws() returns strings concatenated by all strings that are delimited with a separator.

Syntax: concat_ws(<separator>,<string1>,<string2>,... )

The function requires at least two or more strings.

If the separator is NULL, the concat_ws() function returns NULL.

If the separator is not NULL and there is only one string, the string itself is returned.

If there is a NULL in the strings, NULL is ignored during the concatenation.

Example:

//This example concatenates a, b, and c with the separator +.
nebula> RETURN concat_ws("+","a","b","c") AS r;
+---------+
| r       |
+---------+
| "a+b+c" |
+---------+

//In this example, the separator is NULL.
neubla> RETURN concat_ws(NULL,"a","b","c") AS r;
+----------+
| r        |
+----------+
| __NULL__ |
+----------+

//In this example, the separator is + and there is a NULL in the strings.
nebula> RETURN concat_ws("+","a",NULL,"b","c") AS r;
+---------+
| r       |
+---------+
| "a+b+c" |
+---------+

//In this example, the separator is + and there is only one string.
nebula> RETURN concat_ws("+","a") AS r;
+-----+
| r   |
+-----+
| "a" |
+-----+
nebula> GO FROM "player100" over follow \
        YIELD concat_ws(" ",src(edge), properties($^).age, properties($$).name, properties(edge).degree) AS A;
+---------------------------------+
| A                               |
+---------------------------------+
| "player100 42 Tony Parker 95"   |
| "player100 42 Manu Ginobili 95" |
+---------------------------------+

extract()¶

extract() uses regular expression matching to retrieve a single substring or all substrings from a string.

Syntax: extract(<string>,"<regular_expression>")

string: A specified string
regular_expression: A regular expression
Result type: List

Example:

nebula> MATCH (a:player)-[b:serve]-(c:team{name: "Lakers"}) \
        WHERE a.player.age > 45 \
        RETURN extract(a.player.name, "\\w+") AS result;
+----------------------------+
| result                     |
+----------------------------+
| ["Shaquille", "O", "Neal"] |
+----------------------------+

nebula> MATCH (a:player)-[b:serve]-(c:team{name: "Lakers"}) \
        WHERE a.player.age > 45 \
        RETURN extract(a.player.name, "hello") AS result;
+--------+
| result |
+--------+
| []     |
+--------+

json_extract()¶

json_extract() converts the specified JSON string to a map.

Syntax: extract(<string>)

string:A specified string, must be JSON string.
Result type: Map

Caution

Only Bool, Double, Int, String value and NULL are supported.
Only depth-1 nested Map is supported now. If nested Map depth is greater than 1, the nested item is left as an empty Map().

Example:

nebula> YIELD json_extract('{"a": 1, "b": {}, "c": {"d": true}}') AS result;
+-----------------------------+
| result                      |
+-----------------------------+
| {a: 1, b: {}, c: {d: true}} |
+-----------------------------+

Last update: December 21, 2022

Built-in string functions¶

Precautions¶

strcasecmp()¶

lower() and toLower()¶

upper() and toUpper()¶

length()¶

trim()¶

ltrim()¶

rtrim()¶

left()¶

right()¶

lpad()¶

rpad()¶

substr() and substring()¶

Explanations for the return of substr() and substring()¶

reverse()¶

replace()¶

split()¶

concat()¶

concat_ws()¶

extract()¶

json_extract()¶

Explanations for the return of `substr()` and `substring()`¶