Skip to content
Developer Edition runs on one host and does not require a product license file. Need multi-host deployment or performance testing at scale? Explore Enterprise Edition

String functions

substring()

Returns a substring of a string. The substring is of length len and starts from position idx. Positions start from 1. len must be positive.

Null behavior: default

Function signature

substring(str: STRING, idx: INT32, len: INT32) -> STRING

Examples

RETURN substring("abcdef", 1,2) AS substr

Return:

+--------+
| substr |
+--------+
| ab     |
+--------+

repeat()

Returns a new string by concatenating str repeated count times.

Null behavior: default

Function signature

repeat(str: STRING, count: INT32) -> STRING
  • Returns an empty string when count is less than or equal to 0.
  • Returns an error if the result exceeds 2³²−1 (4,294,967,295) bytes.

Examples

Repeat a string twice

RETURN repeat("hello ", 2) AS s

Return:

+--------------+
| s            |
+--------------+
| hello hello  |
+--------------+

Return an empty string when count is negative

RETURN repeat("hello", -1) AS s

Return:

+---+
| s |
+---+
|   |
+---+

Error when result exceeds length limit

RETURN length(repeat("xxxxxxxxx", 1000000000))

Return:

Error: [NR016]: String should be no more than 4294967295 bytes...

fmt()

Returns a string by formatting the given value(s) and placing them into placeholders within the source string.

Null behavior: general

Function signature

fmt(str: STRING, value: STRING | Numeric | BOOLEAN [, value...]) -> STRING

The Numeric data type does not include the DECIMAL data type.

Parameters

  • str: STRING: The source string with one or more placeholders. To make the placeholder work, ensure the placeholder matches the data type of the value to be inserted. The supported placeholders and data types are as follows:
    • %s: STRING
    • %d: UINT8, UINT16, UINT32, UINT64, INT8, INT16, INT32, INT64
    • %f: FLOAT, DOUBLE
    • %b: BOOLEAN
  • value: STRING | Numeric | BOOLEAN [, value...]: The value(s) to be inserted into the placeholder in the source string. Ensure the data types of the value parameters are consistent for each function call. Based on the data types and order of the value parameters, the system matches and replaces the placeholders in the source string with the corresponding values.

Examples

Insert multiple string values into a string

RETURN fmt('hello %s %s', 'my', 'world') AS a

Return:

+----------------+
| a              |
+----------------+
| hello my world |
+----------------+

Insert a boolean value into a string

RETURN fmt("a:%b", true) AS a

Return:

+--------+
| a      |
+--------+
| a:true |
+--------+

Insert multiple types of values into a string

RETURN fmt(fmt(fmt("12%d%s,%d5%d%b,%d%d%b", 3,4,6,78,9),"xyz"),false,true) AS a

Return:

+-------------------------+
| a                       |
+-------------------------+
| 123xyz,456false,789true |
+-------------------------+

left()

Returns the left part of a character string with the specified number of characters.

Null behavior: default

Function signature

left(str: STRING, len: INT32) -> STRING

Examples

RETURN left("abcdef", 3) AS substr

Return:

+--------+
| substr |
+--------+
| abc    |
+--------+

Returns the right part of a character string with the specified number of characters.

Null behavior: default

Function signature

right(str: STRING, len: INT32) -> STRING

Examples

RETURN right("abcdef", 3) AS substr

Return:

+--------+
| substr |
+--------+
| def    |
+--------+

upper()

Returns a string with all characters converted to uppercase.

Null behavior: default

Function signature

upper(str: STRING) -> STRING

Examples

RETURN upper("abCDef") AS a

Return:

+--------+
| a      |
+--------+
| ABCDEF |
+--------+

lower()

Returns a string with all characters converted to lowercase.

Null behavior: default

Function signature

lower(str: STRING) -> STRING

Examples

RETURN lower("abCDef") AS a

Return:

+--------+
| a      |
+--------+
| abcdef |
+--------+

trim()

Returns a string with the leading, trailing, or both characters removed.

The function trim() can also be used to return a list after removing a specified number of elements from the end of the list. For more information, see List function trim().

Null behavior: default

Function signature

trim( [ [ LEADING | TRAILING | BOTH ] [ trim_char: STRING ] FROM ] source_str: STRING ) -> STRING

Parameters

  • LEADING | TRAILING | BOTH: The side of the string to trim. If omitted, the default is BOTH.
  • trim_char: STRING: The character to be removed from the source string. If omitted, the default is the space () character.
  • FROM: If this keyword is specified, you must specify at least one of the optional parameters: the side of the string to trim or the character to be removed.
  • source_str: STRING: The source string.

Examples

Remove leading and trailing space characters

RETURN trim("   abc   ") AS a

Return:

+-----+
| a   |
+-----+
| abc |
+-----+

Remove leading and trailing underscores

RETURN trim('_' FROM "__abc__") AS a

Return:

+-----+
| a   |
+-----+
| abc |
+-----+

Remove leading underscores

RETURN trim(LEADING '_' FROM "__abc__") AS a

Return:

+-------+
| a     |
+-------+
| abc__ |
+-------+

Remove trailing underscores

RETURN trim(TRAILING '_' FROM "__abc__") AS a

Return:

+-------+
| a     |
+-------+
| __abc |
+-------+

ltrim()

Returns a string with the leading characters removed.

Null behavior: default

Function signature

ltrim( source_str: STRING [ , trim_str: STRING ] ) -> STRING

Parameters

  • source_str: STRING: The source string.
  • trim_str: STRING: The string of characters to be removed from the left side of the source string. If omitted, the default is the space () character.

Examples

Remove leading space characters

RETURN ltrim('  abc   ') AS a

Return:

+--------+
| a      |
+--------+
| abc    |
+--------+

Remove leading characters abc from the source string

RETURN ltrim('aabbccdd' , 'abc') AS a

Return:

+----+
| a  |
+----+
| dd |
+----+

rtrim()

Returns a string with the trailing characters removed.

Null behavior: default

Function signature

rtrim( source_str: STRING [, trim_str: STRING] ) -> STRING

Parameters

  • source_str: STRING: The source string.
  • trim_str: STRING: The string of characters to be removed from the right side of the source string. If omitted, the default is the space () character.

Examples

Remove trailing space characters

RETURN rtrim('  abc   ') AS a

Return:

+-------+
| a     |
+-------+
|   abc |
+-------+

Remove trailing characters cd from the source string

RETURN rtrim('aabbccdd' , 'cd') AS a

Return:

+------+
| a    |
+------+
| aabb |
+------+

length()

Returns the number of characters in a string.

The function length() can also be used to return the number of elements in a list or the length of a path. For more information, see List function length() and Graph function length().

Null behavior: default

Function signature

length(str: STRING) -> INT32

Examples

RETURN length('nebula graph') AS l

Return:

+----+
| l  |
+----+
| 12 |
+----+

contains()

Returns true if a specified substring is found within a string.

Null behavior: default

Function signature

contains(str: STRING, substr: STRING) -> BOOLEAN

Parameters

  • str: STRING: The source string to be matched.
  • substr: STRING: The substring to be found.

Both str and substr can be expressions that return a string.

Examples

RETURN contains('nebula graph', 'nebula') AS c

Return:

+------+
| c    |
+------+
| true |
+------+

like()

Returns true if a string matches a specified pattern. It's used for pattern matching on string values. The pattern can include two wildcards:

  • percent sign (%): matches any sequence of characters, including no characters.
  • underscore (_): matches any single character.

To match the percent sign (%) and underscore (_) as regular characters rather than wildcards within the pattern, add a backslash (\) before the percent sign (%) and underscore (_) respectively. The backslash (\) itself is an escape character, so to match it, use \\. For example, use \\% to match the % character and \\_ to match the _ character. An error is returned if the pattern is invalid.

Null behavior: default

Function signature

like(str: STRING, pattern: STRING) -> BOOLEAN

or

str: STRING LIKE pattern: STRING -> BOOLEAN

Both str and pattern can be an expression that returns a string.

Examples

Match with a simple pattern

RETURN like("a", "a%%") AS v

Return:

+------+
| v    |
+------+
| true |
+------+

Match with the % wildcard

RETURN "hello, world" LIKE "hello, wo%" AS v

Return:

+------+
| v    |
+------+
| true |
+------+

Match with the _ wildcard

RETURN "hello, world" LIKE "hello, _orl_" AS v

Return:

+------+
| v    |
+------+
| true |
+------+

Match with the escape character

RETURN '%' LIKE '\\%' AS v, '_' LIKE '\\_' AS t

Return:

+------+------+
| v    | t    |
+------+------+
| true | true |
+------+------+

Match with expressions

'''
LET input = "hello, world", patterns = LIST[ 
  "%ello, _orl%",
  "hello, _orld%",
  "%hello, world%%"]
FOR i in RANGE(1, LENGTH(patterns))
RETURN input LIKE  patterns[i-1] AS v
'''

The above example uses the like() function to match the input string with the patterns in the list. The i-1 index is used to access the elements in the list. The FOR statement is used to iterate through the list. The length() function is used to get the length of the list. The range() function is used to generate a list that starts from 1 and ends at the length value. For more information, see FOR and List functions.

Return:

+------+
| v    |
+------+
| true |
| true |
| true |
+------+

Invalid pattern

RETURN 'a' LIKE '\\a'
Return:

[ERROR]: Invalid function argument `\a` of `like`

regexp_like()

Returns true if a string matches a specified regular expression. regexp_like() is similar to like(), but uses a regular expression pattern for matching. An error is returned if the regular expression pattern is invalid.

Null behavior: default

Function signature

regexp_like(str: STRING, pattern: STRING [, options: STRING]) -> BOOLEAN

Parameters

  • str: STRING: The source string to be matched.
  • pattern: STRING: The regular expression pattern to be found. For more information about regular expression syntax, see Regular expressions.

  • options: STRING: The options for the regular expression matching. The following options are supported:

    • i: case-insensitive matching
    • c: case-sensitive matching
    • n: multiline matching
    • m: synonymous with n

    The options parameter is optional. If omitted, the default is case-sensitive and single-line matching. If specified, it must be a string that contains one or more of the supported options, and any other characters are ignored. The options can be combined in any order.

Both str and pattern can be an expression that returns a string.

Examples

Match with a simple regular expression

RETURN regexp_like("hello", "he..o") AS v

Return:

+------+
| v    |
+------+
| true |
+------+

Match with regular expression options

RETURN regexp_like("hello\n, world", "HELLO.*", 'inx') AS v

The above example uses the i option to perform case-insensitive matching and the n option to perform multiline matching. The x option is ignored because it is not a valid option.

Return:

+------+
| v    |
+------+
| true |
+------+

Match with str and pattern expressions

'''
LET words = LIST["hello, world"] 
LET patterns = LIST["hel{2}o, .(or).*", ".*", "^hello.*", "hello"] 
FOR w IN words 
FOR p IN patterns 
RETURN regexp_like(w, p) AS v
'''

The above example uses the regexp_like function to match the words in the list with the patterns in the list. The w and p variables are used to access the elements in the lists. The FOR statement is used to iterate through the lists. For more information, see FOR and List functions.

Return:

+-------+
| v     |
+-------+
| true  |
| true  |
| true  |
| false |
+-------+

Invalid regular expression

RETURN regexp_like("a", "(a") AS v

Return:

[ERROR]: Invalid function argument `(a` of `regexp_like`

split()

Splits the given string into a list of substrings using a given regular expression as a separator.

Null behavior: default

Function signature

split(str: STRING, regex: STRING[, limit: UINT]) -> LIST<STRING>

Parameters

  • str: STRING: The source string to be split.
  • regex: STRING: The regular expression to be used as a separator.
  • limit: UINT: An optional unsigned integer (negative values are treated as 0). It specifies the maximum number of splits to be returned. If omitted (default is 0) or if the value exceeds the number of all possible splits, all possible splits are returned.

Examples

Split a string with a comma separator

RETURN split("a,b,c", ",") AS result

Return:

+---------+
| result  |
+---------+
| [a,b,c] |
+---------+

Split a string with a comma separator and a limit of 2

RETURN split("a,b,c", ",", 2) AS result

Return:

+-----------+
| result    |
+-----------+
| [a,"b,c"] |
+-----------+

Split a string with a regular expression separator

RETURN split("a.txt,.txtb .txt,dfdc.txt, .txt ","[a-z]+\\.txt") AS result

Return:

+-----------------------------+
| result                      |
+-----------------------------+
| [,",.txtb .txt,",", .txt "] |
+-----------------------------+

md5()

Returns the MD5 hash of a string or a list of strings.

Null behavior: default

Function signature

md5(str: STRING | l: LIST<STRING>) -> STRING

Parameters

  • str: STRING: The source string to be hashed.
  • l: LIST<STRING>: The list of source strings to be hashed. The elements in the list are concatenated into a single string before computing the hash.

Examples

Return the MD5 hash of a string

RETURN md5("Hello World") AS hash

Return:

+----------------------------------+
| hash                             |
+----------------------------------+
| b10a8db164e0754105b7a99be72e3fe5 |
+----------------------------------+

Return the MD5 hash of a list of strings

RETURN md5(["The","is","not","real"]) AS hash

Return:

+----------------------------------+
| hash                             |
+----------------------------------+
| 2959ae045f5aecdf022770443d6a85d8 |
+----------------------------------+

Compare the MD5 hashes of two lists of strings

LET a = md5(["ThisIs", "Apple"]), b = md5(["This", "IsApple"]) RETURN a, b, a=b AS c

Return:

+----------------------------------+----------------------------------+------+
| a                                | b                                | c    |
+----------------------------------+----------------------------------+------+
| 40a699aa16fce58504ca8a4c277f3f4c | 40a699aa16fce58504ca8a4c277f3f4c | true |
+----------------------------------+----------------------------------+------+

index_of()

Returns the starting position of the first occurrence of the specified substring in the target string, or -1 if not found. You can specify the start and end positions to limit the search range. The search range is the half-open interval [from, to).

Null behavior: default

Function signature

index_of(str: STRING, substr: STRING[, from: INT, to: INT]) -> INT

Parameters

  • str: STRING: The target string to search in.
  • substr: STRING: The substring to search for in the target string str.
  • from: INT (optional): The start position (inclusive) of the search range. Zero-based. Defaults to 0. Negative values are treated as 0.
  • to: INT (optional): The end position (exclusive) of the search range. Zero-based. Defaults to -1, which means searching to the end of the string. Negative values or values greater than the length of str are treated as the length of str.

Examples

Find the first occurrence of a substring

RETURN index_of("a数据库✅bcadabad书ad", "ad") AS res

Return:

+-----+
| res |
+-----+
| 7   |
+-----+

Find the first occurrence of a substring with a specified range

RETURN index_of("a数据库✅bcadabad书ad", "ad", 12) AS res

Return:

+-----+
| res |
+-----+
| 14  |
+-----+

The preceding statement returns the position of the substring ad starting from the 12th position of the target string, which is the first occurrence of ad after that position.

Return -1 when the substring is not found

RETURN index_of("a数据库✅bcadabad书ad", "ad", 12, 13) AS res

Return:

+-----+
| res |
+-----+
| -1  |
+-----+

indexes_of()

Returns a list of all start positions of a specified substring within a string, or empty list if the substring is not found. The search range can be limited by specifying start and end positions.

Null behavior: default

Function signature

indexes_of(str: STRING, substr: STRING[, from: INT, to: INT]) -> LIST<INT>

Parameters

  • str: STRING: The string to search within.
  • substr: STRING: The substring to search for.
  • from: INT (optional): The zero-based position to start the search. Defaults to 0. Negative values are treated as 0.
  • to: INT (optional): The zero-based position to end the search. Defaults to the length of the target string (str). Negative values or values greater than the length of the target string are treated as the length of the target string.

Examples

Find all the start positions of a substring

RETURN indexes_of("a数据库✅bcadabad书ad", "ad") AS res

Return:

+----------------+
| res            |
+----------------+
| LIST [7,11,14] |
+----------------+

Find all the start positions of a substring within a specified range

RETURN indexes_of("a数据库✅bcadabad书ad", "ad", 12) AS res

Return:

+------+
| res  |
+------+
| [14] |
+------+

Return an empty list when the substring is not found

RETURN indexes_of("a数据库✅bcadabad书ad", "ad", 12, 13) AS res

Return:

+-----+
| res |
+-----+
| []  |
+-----+

to_literal()

Returns the string representation of the input value.

Null behavior: general

Function signature

to_literal(input: E) -> STRING

E represents any data type except DURATION and PATH, and table type.

Examples

Convert predefined data types to string literals

'''
RETURN to_literal(-12) AS a, 
    to_literal(12.34f) AS b, 
    to_literal(null) AS c, 
    to_literal(true) AS d,
    to_literal(CAST(1.5 AS UINT64)) AS e, 
    to_literal(DATE '2022-12-31') AS f,
    to_literal(VECTOR<3,float>([1.1,2.1,3.1])) AS g,
    to_literal("hello world") AS h
'''

Return:

+-----+--------+------+------+---+------------------------------------+------------------------+---------------+
| a   | b      | c    | d    | e | f                                  | g                      | h             |
+-----+--------+------+------+---+------------------------------------+------------------------+---------------+
| -12 | 12.34f | NULL | true | 2 | date(\"2022-12-31\", \"%Y-%m-%d\") | VECTOR(1.1f,2.1f,3.1f) | 'hello world' |
+-----+--------+------+------+---+------------------------------------+------------------------+---------------+

Convert constructed data types to string literals

'''
RETURN to_literal([1,2,3,4.5]) AS a, 
    to_literal({a:1, b:true}) AS b,
    to_literal(SET {"a", "b", "c"}) AS c,
    to_literal(MAP{"a":1, "b":2}) AS d
'''

Return:

+---------------------+--------------------+------------------+------------------+
| a                   | b                  | c                | d                |
+---------------------+--------------------+------------------+------------------+
| LIST[1M,2M,3M,4.5M] | RECORD{a:1,b:true} | SET{'a','b','c'} | MAP{'a':1,'b':2} |
+---------------------+--------------------+------------------+------------------+

Convert a table type to a string literal

'''
TABLE t TYPED TABLE { id INT64, name STRING } =  
    { id: 10001, name: 'Alice' },  
    { id: 10002, name: 'Bob' }
RETURN to_literal(t) AS tableLiteral
'''

Return:

+-------------------------------+
| tableLiteral                  |
+-------------------------------+
| (10001,'Alice'),(10002,'Bob') |
+-------------------------------+

Convert special values to a string literal

'''
RETURN to_literal(1.0d/0.0) AS inf_0, 
       to_literal(-1.0f/0.0) AS inf_1,
       to_literal(CAST('NaN' AS DECIMAL)) AS `nan`
'''

Return:

+-----------------------------+------------------------------+------------------------+
| inf_0                       | inf_1                        | nan                    |
+-----------------------------+------------------------------+------------------------+
| CAST('Infinity' AS FLOAT64) | CAST('-Infinity' AS FLOAT32) | CAST('NaN' AS DECIMAL) |
+-----------------------------+------------------------------+------------------------+

The preceding example returns the string literals for positive infinity (1.0d/0.0), negative infinity (-1.0d/0.0), and not-a-number (NaN).