Skip to content

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() 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: October 25, 2023