String Functions

String Functions

String functions perform operations on a string input value and returns a string or other value.

CONTAINS(expression, substring)

True if the string contains the substring.

INITCAP(expression), TITLE(expression)

Converts the string so that the first letter of each word is uppercase and every other letter is lowercase.

LENGTH(expression)

Returns length of the string value.

LOWER(expression)

Returns lowercase of the string value.

LTRIM(expression [, characters ])

Returns string with all leading characters removed. White space is trimmed by default. The function removes leading characters from the expression that match the specified characters and stops when it encounters a character that does not match any of the specified characters.

POSITION(expression, substring)

Returns the first position of the substring within the string, or -1. The position is zero-based, i.e., the first position is 0.

REPEAT(expression, n)

Returns string formed by repeating expression n times.

REPLACE(expression, substring, repl [, n ])

Returns string with all occurrences of substr replaced with repl. If n is given, at most n replacements are performed.

REVERSE(expression)

This function reverses a given string.

Example
SELECT REVERSE("won thgiarts ma I");
[ 
  {    
    "$1": "I am straight now"  
  }
]

RTRIM(expression, [, characters ])

Returns string with all trailing characters removed. White space is trimmed by default. The function removes trailing characters from the expression that match the specified characters and stops when it encounters a character that does not match any of the specified characters.
select rtrim("2013-06-22 18:33:30 330+0300", "+03") ;
          
          "results": [
          {
          "$1": "2013-06-22 18:33:30 "
          }
          
          select rtrim("2013-06-22 18:33:30 330+0300", " +03") ;
          
          "results": [
          {
          "$1": "2013-06-22 18:33:"
          }

SPLIT(expression [, sep ])

Splits the string into an array of substrings separated by the specified separator sep. If sep is not given, any combination of white space characters is used.

SUBSTR(expression, position [, length ])

Returns substring from the integer position of the given length, or to the end of the string. 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.

SUFFIXES(expression)

Since Couchbase Server 4.5

Generates an array of all the suffixes of the input string.

Example

The following example uses the SUFFIXES() function to query the airport names when a partial airport name is given.
Create Index
CREATE INDEX autocomplete_airport_name 
          ON `travel-sample`( DISTINCT ARRAY array_element FOR array_element IN SUFFIXES(LOWER(airportname)) END )
          WHERE type = "airport";
Query
SELECT airportname 
            FROM `travel-sample`
            WHERE
            ANY array_element IN SUFFIXES(LOWER(airportname)) SATISFIES array_element LIKE 'washing%' END
            AND type="airport";
Results
[
              {
              "airportname": "Washington Dulles Intl"
              },
              {
              "airportname": "Baltimore Washington Intl"
              },
              {
              "airportname": "Ronald Reagan Washington Natl"
              },
              {
              "airportname": "Washington Union Station"
              }
              ]

The following blog provides more information about this example.

TITLE(expression), INITCAP(expression)

Converts the string so that the first letter of each word is uppercase and every other letter is lowercase.

TOKENS()

This function tokenizes 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.

The important thing to note is that first expression passed into token 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 can take the following options, and each invocation of TOKENS() can choose one or more of the options, passed in as JSON.
  • {"name": true}: Optional. Valid values are true or false. By default, this is set to true and TOKENS(expr) will include field names. You can choose to not include field names by setting this option to false, such as TOKENS(expr, 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.
    Examples
    SELECT  TOKENS( expr,
    { "names" : true, "case" : "lower" }
    );
    
    SELECT TOKENS( expr,
    { "case" : "lower" }
    );
    
    SELECT TOKENS( expr,
    { "names" : false }
    )
    
    SELECT TOKENS( expr );
  • {"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.
    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"
        ]
      }
    ]
    
    

Examples

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
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' ;
Query
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' ;

{
    "requestID": "6f748683-6529-476a-bdad-82cd8e529380",
    "clientContextID": "ce5e77fc-0c27-424c-a49d-4160884f1b37",
    "signature": {
        "address": "json",
        "name": "json",
        "url": "json"
    },
    "results": [
        {
            "address": "Capstone Road, ME7 3JE",
            "name": "Medway Youth Hostel",
            "url": "http://www.yha.org.uk"
        }
    ],
    "status": "success",
    "metrics": {
        "elapsedTime": "11.872615ms",
        "executionTime": "11.843406ms",
        "resultCount": 1,
        "resultSize": 146
    }
}

TRIM(expression [, characters ])

Returns string with all leading and trailing chars removed. White space by default.

UPPER(expression)

Returns uppercase of the string value.

The following example shows the use of a LOWER string function.

SELECT product
        FROM product
        UNNEST product.categories as categories
        WHERE LOWER(categories) = "appliances"