String Functions

String Functions

String functions perform operations on a string input value and returns a string or other value.
Note: If any arguments to any of the following functions are MISSING then the result is also MISSING (i.e. no result is returned). Similarly, if any of the arguments passed to the functions are NULL or are of the wrong type (e.g. an integer instead of a string), then NULL is returned as the result.

CONTAINS(input_string, search_string)

Description
Checks whether or not the specified search string is a substring of the input string (i.e. exists within). This returns true if the substring exists within the input string, otherwise false is returned.
Arguments
input_string
A string, or any valid expression which evaluates to a string, that is the string to search within.
search_string
A string, or any valid expression which evaluates to a string, that is the string to search for.
Return Value
A boolean, representing whether the search string exists within the input string.
Examples
SELECT CONTAINS("N1QL is awesome", "N1QL") as n1ql,
CONTAINS("N1QL is awesome", "SQL") as no_sql;
              
{
    "results": [
        {
            "n1ql": true,
            "no_sql": false
        }
    ]
}

INITCAP(input_string)

Description
Converts the string so that the first letter of each word is uppercase and every other letter is lowercase (known as 'Title Case').
Arguments
input_string
A string, or any valid expression which evaluates to a string, that is the string to convert to title case.
Return Value
A string, representing the input string converted to title case.
Limitations
This function capitalizes the initial letter of every word in the sentence, this means that even short words such as "the" and "or" will be capitalized. This does not strictly follow title case conventions used in the writing domain.
Examples
SELECT INITCAP("N1QL is awesome") as n1ql;
              
{
    "results": [
        {
            "n1ql": "N1ql Is Awesome"
        }
    ]
}

LENGTH(input_string)

Description
Finds the length of a string, the length is defined as the number of characters within the string.
Arguments
input_string
A string, or any valid expression which evaluates to a string, that is the string to find the length of.
Return Value
An integer representing the length of the string.
Examples
SELECT LENGTH("") as empty_string,
LENGTH("N1QL is awesome") as n1ql;
              
{    
    "results": [
        {
            "empty_string": 0,
            "n1ql": 15
        }
    ]
}

LOWER(input_string)

Description
Converts all characters in the input string to lower case. This is useful for canonical comparison of string values.
Arguments
input_string
A string, or any valid expression which evaluates to a string, that is the string to convert to lower case.
Return Value
A string representing the input string converted to lower case.
Examples
SELECT LOWER("N1QL is awesome") as n1ql;
              
{    
    "results": [
        {
            "n1ql": "n1ql is awesome"
        }
    ]
}

LTRIM(input_string [, characters ])

Description
Removes all leading characters from a string. The function removes all consecutive characters from the beginning of the string that match the specified characters and stops when it encounters a character that does not match any of the specified characters.
Arguments
input_string
A string, or any valid expression which evaluates to a string, that is the string to remove the leading characters from.
characters
Optional argument, if unspecified then this will default to whitespace (i.e. " ").
A string, or any valid expression which evaluates to a string, that represents the characters to trim from the input string. Each character in this string will be trimmed from the input string, it is therefore not necessary to delimit the characters to trim.. For example specifying a character value of "abc" will trim the characters "a", "b" and "c" from the start of the string.
Return Value
A string representing the input string with leading characters removed.
Examples
SELECT LTRIM("...N1QL is awesome", ".") as dots,
    LTRIM("     N1QL is awesome", " ") as explicit_spaces,
    LTRIM("     N1QL is awesome") as implicit_spaces,
    LTRIM("N1QL is awesome") as no_dots;

{
    "results": [
        {
            "dots": "N1QL is awesome",
            "explicit_spaces": "N1QL is awesome",
            "implicit_spaces": "N1QL is awesome",
            "no_dots": "N1QL is awesome"
        }
    ]
}

POSITION(input_string, search_string)

Description
Finds the first position of the search string within the string, this position is zero-based, i.e., the first position is 0. If the search string does not exist within the input string then the function returns -1.
Arguments
input_string
A string, or any valid expression which evaluates to a string, that is the string to search within.
search_string
A string, or any valid expression which evaluates to a string, that is the string to search for.
Return Value
An integer representing the first position of the search string.
Examples
SELECT POSITION("N1QL is awesome", "awesome") as awesome, 
    POSITION("N1QL is awesome", "N1QL") as n1ql,
    POSITION("N1QL is awesome", "SQL") as sql
              
{    
    "results": [
        {
            "awesome": 8,
            "n1ql": 0,
            "sql": -1
        }
    ]
}

Returns

REPEAT(input_string, n)

Description
Creates a new string which is the input string repeated the specified number of times.
Arguments
input_string
A string, or any valid expression which evaluates to a string, that is the string to repeat.
n
An integer, or any valid expression which evaluates to an integer, that is the number of times to repeat the string.
Return Value
A string representing the string generated by repeating the input string.
Limitations
It is possible to generate very large strings using this function. In some cases the query engine may be unable to process all of these and cause excessive resource consumption. It is therefore recommended that you first validate the inputs to this function to ensure that the generated result is a reasonable size.
Examples
SELECT REPEAT("N1QL", 0) as empty_string,
REPEAT("N1QL", 3) as n1ql_3

{    
    "results": [
        {
            "empty_string": "",
            "n1ql_3": "N1QLN1QLN1QL"
        }
    ]
}

REPLACE(input_string, search_string, replacement [, n ])

Description
Replaces occurrences of a given substring in an input string.
Arguments
input_string
A string, or any valid expression which evaluates to a string, that is the string to search for replacements in.
search_string
A string, or any valid expression which evaluates to a string, that is the string to replace.
replacement
A string, or any valid expression which evaluates to a string, that is the string to replace the search string with.
n
Optional argument, if unspecified then by default all instances of the search string are replaced.
An integer, or any valid expression which evaluates to an integer, which represents the number of instances of the search string to replace. If a negative value is specified then all instances of the search string are replaced.
Return Value
A string representing the input string with the specified substring replaced.
Examples
SELECT REPLACE("SQL SQL SQL", "S", "N1", -2) as negative_n,
    REPLACE("SQL SQL SQL", "S", "N1", 2) as replace_2,
    REPLACE("SQL SQL SQL", "S", "N1") as replace_all
              
{    
    "results": [
        {
            "negative_n": "N1QL N1QL N1QL",
            "replace_2": "N1QL N1QL SQL",
            "replace_all": "N1QL N1QL N1QL"
        }
    ]
}

REVERSE(input_string)

Description
Reverses the order of the characters in a given string. i.e. The first character becomes the last character and the last character becomes the first character etc. This is useful for testing whether or not a string is a palindrome.
Arguments
input_string
A string, or any valid expression which evaluates to a string, that is the string to reverse.
Return Value
A string representing the input string with its characters reversed.
Examples
SELECT REVERSE("N1QL is awesome") as n1ql,
    REVERSE("racecar") as palindrome
              
{    
    "results": [
        {
            "n1ql": "emosewa si LQ1N",
            "palindrome": "racecar"
        }
    ]
}

RTRIM(input_string [, characters ])

Description
Removes all trailing characters from a string. The function removes all consecutive characters from the end of the string that match the specified characters and stops when it encounters a character that does not match any of the specified characters.
Arguments
input_string
A string, or any valid expression which evaluates to a string, that is the string to convert to remove trailing characters from.
characters
Optional argument, if unspecified then this will default to whitespace (i.e. " ").
A string, or any valid expression which evaluates to a string, that represents the characters to trim from the input string. Each character in this string will be trimmed from the input string, it is therefore not necessary to delimit the characters to trim. For example specifying a character value of "abc" will trim the characters "a", "b" and "c" from the start of the string.
Return Value
A string representing the input string with its trailing characters removed.
Examples
SELECT RTRIM("N1QL is awesome...", ".") as dots,
    RTRIM("N1QL is awesome     ", " ") as explicit_spaces,
    RTRIM("N1QL is awesome     ") as implicit_spaces,
    RTRIM("N1QL is awesome") as no_dots;
              
{    
    "results": [
        {
            "dots": "N1QL is awesome",
            "explicit_spaces": "N1QL is awesome",
            "implicit_spaces": "N1QL is awesome",
            "no_dots": "N1QL is awesome"
        }
    ]
}

SPLIT(input_string [, sep ])

Description
Splits the string into an array of substrings, based on the specified separator string.
Arguments
input_string
A string, or any valid expression which evaluates to a string, that is the string to split.
sep
A string, or any valid expression which evaluates to a string, that is the substring to split the input string on.
Return Value
An array of strings containing the strings created by splitting the input string.
Examples
SELECT SPLIT("N1QL is awesome", " ") as explicit_spaces,
    SPLIT("N1QL is awesome") as implicit_spaces,
    SPLIT("N1QL is awesome", "is") as split_is
              
{    
    "results": [
        {
            "explicit_spaces": [
                "N1QL",
                "is",
                "awesome"
            ],
            "implicit_spaces": [
                "N1QL",
                "is",
                "awesome"
            ],
            "split_is": [
                "N1QL ",
                " awesome"
            ]
        }
    ]
}

SUBSTR(input_string, start_pos [, length ])

Description
Returns the substring (of given length) starting at the provided position. The position is zero-based, i.e. the first position is 0. If position is negative, it is counted from the end of the string; -1 is the last position in the string.
Arguments
input_string
A string, or any valid expression which evaluates to a string, that is the string to convert to extract the substring from.
start_pos
An integer, or any valid expression which evaluates to an integer, that is the start position of the substring.
length
Optional argument, defaults to capturing to the end of the string if unspecified.
An integer, or any valid expression which evaluates to an integer, that is the length of the substring to extract.
Return Value
A string representing the substring extracted from the input string.
Examples
SELECT SUBSTR("N1QL is awesome", 3) as end_of_string,
    SUBSTR("N1QL is awesome", 3, 1) as single_letter,
    SUBSTR("N1QL is awesome", 3, 3) as three_letters
              
{    
    "results": [
        {
            "end_of_string": "L is awesome",
            "single_letter": "L",
            "three_letters": "L i"
        }
    ]
}

SUFFIXES(input_string)

Description
Generates an array of all the suffixes of the input string.
Arguments
input_string
A string, or any valid expression which evaluates to a string, that is the string to generate the suffixes of.
Return Value
An array of strings containing all of the suffixes of the input string.
Examples
SELECT SUFFIXES("N1QL is awesome") as n1ql

{
    "results": [
        {
            "n1ql": [
                "N1QL is awesome",
                "1QL is awesome",
                "QL is awesome",
                "L is awesome",
                " is awesome",
                "is awesome",
                "s awesome",
                " awesome",
                "awesome",
                "wesome",
                "esome",
                "some",
                "ome",
                "me",
                "e"
            ]
        }
    ]
}
The following example uses the SUFFIXES() function to index and query the airport names when a partial airport name is given.
CREATE INDEX autocomplete_airport_name 
    ON `travel-sample`( DISTINCT ARRAY array_element FOR array_element IN SUFFIXES(LOWER(airportname)) END )
    WHERE type = "airport";
SELECT airportname 
    FROM `travel-sample`
    WHERE
    ANY array_element IN SUFFIXES(LOWER(airportname)) SATISFIES array_element LIKE 'washing%' END
    AND type="airport";
{
    "results": [
        {
            "airportname": "Ronald Reagan Washington Natl"
        },
        {
            "airportname": "Washington Dulles Intl"
        },
        {
            "airportname": "Baltimore Washington Intl"
        },
        {
            "airportname": "Washington Union Station"
        }
    ]
}

The following blog provides more information about this example.

TITLE(input_string)

Alias for INITCAP().

TOKENS(input_string, options)

Description

This function tokenizes (i.e. breaks up into meaningful segments) the given input string based on specified delimiters, and other options. It recursively enumerates all tokens in a JSON value and returns an array of values (JSON atomic values) as the result.

Arguments
input_string
A valid JSON object, this can be anything: constant literal, simple JSON value, JSON key name or the whole document itself.
The following table lists the rules for each JSON type:
JSON Type Return Value
MISSING []
NULL [NULL]
false [false]
true [true]
number [number]
string SPLIT(string)
array FLATTEN(TOKENS(element) for each element in array

(Concatenation of element tokens)

object For each name-value pair, name+TOKENS(value)
options
A JSON object indicating the options passed to the TOKENS() function. Options can take the following options, and each invocation of TOKENS() can choose one or more of the options:
  • {"name": true}: Optional. Valid values are true or false. By default, this is set to true and TOKENS() will include field names. You can choose to not include field names by setting this option to false.
  • {"case":"lower"}: Optional. Valid values are lower or upper. Default is neither — return the case in the data. Use this option to specify the case sensitivity.
  • {"specials": true}:Optional. Use this option to preserve strings with specials characters, such as email addresses, URLs, and hyphenated phone numbers. The default value is false.
    Note: The specials options preserves special characters except at the end of a word.
Return Value
An array of strings containing all of the tokens obtained from the input string.
Examples
SELECT TOKENS( ['jim@abc.com, kim@abc.com, http://abc.com/, 408-555-1212'],
    { 'specials': false });

[
    {
        "$1": [
            "kim",
            "408",
            "com",
            "abc",
            "jim",
            "1212",
            "555",
            "http"
        ]
    }
]

SELECT TOKENS(['jim@abc.com, kim@abc.com, http://abc.com/, 408-555-1212'],
	{ 'specials': true });

[
    {
        "$1": [
            "jim",
            "408",
            "1212",
            "jim@abc.com",
            "http",
            "abc",
            "408-555-1212",
            "kim@abc.com",
            "kim",
            "555",
            "http://abc.com",
            "com"
        ]
    }
]

In the following example, TOKENS() converts all of the URL data into UPPER case and also adds the full URL to the delimited words.

SELECT TOKENS(url) AS defaulttoken, 
	TOKENS(url, {"specials":true, "case":"UPPER"}) AS specialtoken
	FROM `travel-sample` h WHERE h.type = 'hotel'
	LIMIT 1;
	
[
    {
        "defaulttoken": [
            "uk",
            "www",
            "http",
            "yha",
            "org"
        ],
        "specialtoken": [
            "ORG",
            "WWW",
            "HTTP://WWW.YHA.ORG.UK",
            "YHA",
            "UK",
            "HTTP"
        ]
    }
]

You can also use {"case":"lower"} or {"case":"upper"} to have case sensitive search. Index creation and querying can use this and other parameters in combination. These parameters should be passed within the query predicates as well. The parameters and values have to match exactly for N1QL to pick up and use the index correctly. The following example shows how you can create an index and use it your application.

CREATE INDEX idx_url_upper_special on `travel-sample`(
    DISTINCT ARRAY v for v in 
        tokens(url, {"specials":true, "case":"UPPER"}) 
    END ) where type = 'hotel';
SELECT name, address, url
    FROM `travel-sample` h
    WHERE ANY  v in tokens(url, {"specials":true, "case":"UPPER"})
    SATISFIES v = "HTTP://WWW.YHA.ORG.UK" 
    END  
    AND h.type = 'hotel' ;

{
    "results": [
        {
            "address": "Capstone Road, ME7 3JE",
            "name": "Medway Youth Hostel",
            "url": "http://www.yha.org.uk"
        }
    ]
}

TRIM(expression [, characters ])

Description
Removes all leading and trailing characters from a string. The function removes all consecutive characters from the beginning and end of the string that match the specified characters and stops when it encounters a character that does not match any of the specified characters. This function is equivalent to calling LTRIM() and RTRIM() successively.
Arguments
input_string
A string, or any valid expression which evaluates to a string, that is the string to convert to remove trailing and leading characters from.
characters
Optional argument, if unspecified then this will default to whitespace (i.e. " ").
A string, or any valid expression which evaluates to a string, that represents the characters to trim from the input string. Each character in this string will be trimmed from the input string, it is therefore not necessary to delimit the characters to trim. For example specifying a character value of "abc" will trim the characters "a", "b" and "c" from the start of the string.
Return Value
A string representing the input string with trailing and leading characters removed.
Examples
SELECT TRIM("...N1QL is awesome...", ".") as dots,
    TRIM("     N1QL is awesome     ", " ") as explicit_spaces,
    TRIM("     N1QL is awesome     ") as implicit_spaces,
    TRIM("N1QL is awesome") as no_dots;

{
    "results": [
        {
            "dots": "N1QL is awesome",
            "explicit_spaces": "N1QL is awesome",
            "implicit_spaces": "N1QL is awesome",
            "no_dots": "N1QL is awesome"
        }
    ]
}

UPPER(input_string)

Description
Converts all characters in the input string to upper case.
Arguments
input_string
A string, or any valid expression which evaluates to a string, that is the string to convert to upper case.
Return Value
A string representing the input string converted to upper case.
Examples
SELECT UPPER("N1QL is awesome") as n1ql;
              
{    
    "results": [
        {
            "n1ql": "N1QL IS AWESOME"
        }
    ]
}