Pattern-matching Functions

Pattern-matching functions allow you to find regular expression patterns in strings or attributes. Regular expressions can formally represent various string search patterns using different special characters to indicate wildcards, positional characters, repetition, optional or mandatory sequences of letters etc. N1QL functions are available to find matching patterns, find position of matching pattern, or replace a pattern with a new string.

For more information on all supported REGEX patterns, see https://golang.org/pkg/regexp/syntax/

Couchbase Server 4.x N1QL supports regular expressions supported by The Go Programming Language version 1.4.2. From Couchbase Server 5.0, The Go Programming Language version 1.8 is supported.

REGEXP_CONTAINS(expression, pattern)

This function has an alias REGEX_CONTAINS().

Arguments

expression: string, or any N1QL expression that evaluates to a string

pattern: string representing a supported regular expression

Return Value

Returns True if the string value contains the regular expression pattern.

Example

SELECT name
FROM `travel-sample`
WHERE type = "landmark" AND REGEXP_CONTAINS(name, ".*In+")
LIMIT 2;

Results

[
  {
    "name": "Beijing Inn"
  },
  {
    "name": "Sportsman Inn"
  }
]

REGEXP_LIKE(expression, pattern)

This function has an alias REGEX_LIKE().

Arguments

expression: string, or any N1QL expression that evaluates to a string

pattern: string representing a supported regular expression

Return Value

Returns True if the string value contains the regular expression pattern.

Example

SELECT name
FROM `travel-sample`
WHERE type = "hotel" and REGEXP_LIKE(name, "In+.*")
LIMIT 4;

Results

[
  {
    "name": "Inveraray Youth Hostel"
  },
  {
    "name": "Inverness Youth Hostel"
  },
  {
    "name": "Indian Cove Campground"
  },
  {
    "name": "Inn at Marina del Rey"
  }
]

REGEXP_POSITION(expression, pattern)

This function has an alias REGEX_POSITION().

Arguments

expression: string, or any N1QL expression that evaluates to a string

pattern: string representing a supported regular expression

Return Value

Returns first position of the occurrence of the regular expression pattern within the input string expression. Returns -1 if no match is found. Position counting starts from zero.

Example

The following query finds positions of first occurrence of vowels in each word of the name attribute.

SELECT name, ARRAY REGEXP_POSITION(x, "[aeiou]") FOR x IN TOKENS(name) END
FROM `travel-sample`
WHERE type = "hotel"
LIMIT 2;

Results

[
  {
    "$1": [
      1,
      1,
      1
    ],
    "name": "Medway Youth Hostel"
  },
  {
    "$1": [
      1,
      2,
      1
    ],
    "name": "The Balmoral Guesthouse"
  }
]

REGEXP_REPLACE(expression, pattern, repl [, n])

This function has an alias REGEX_REPLACE().

Arguments

expression: string, or any N1QL expression that evaluates to a string

pattern: string representing a supported regular expression

repl: string, or any N1QL expression that evaluates to a string

n: the maximum number of times to find and replace the matching pattern

Return Value

Returns new string with occurrences of pattern replaced with repl. If n is given, at the most n replacements are performed. If n is not provided, all matching occurrences are replaced.

Example 1

SELECT REGEXP_REPLACE("N1QL is Sql(infact, sql++) for NoSql", "[sS][qQ][lL]", "SQL"),
       REGEXP_REPLACE("Winning innings Inn", "[Ii]n+", "Hotel", 6),
       REGEXP_REPLACE("Winning innings Inn", "[IiNn]+g", upper("inning"), 2);

Results

[
  {
    "$1": "N1QL is SQL(infact, SQL++) for NoSQL",
    "$2": "WHotelHotelg HotelHotelgs Hotel",
    "$3": "WINNING INNINGs Inn"
  }
]

Example 2

In this example, the query retrieves first 4 documents and replaces the pattern of repeating n with emphasized ‘NNNN’.

SELECT name, REGEXP_REPLACE(name, "n+", "NNNN") as new_name
FROM `travel-sample`
LIMIT 4;

Results

[
  {
    "name": "40-Mile Air",
    "new_name": "40-Mile Air"
  },
  {
    "name": "Texas Wings",
    "new_name": "Texas WiNNNNgs"
  },
  {
    "name": "Atifly",
    "new_name": "Atifly"
  },
  {
    "name": "Jc royal.britannica",
    "new_name": "Jc royal.britaNNNNica"
  }
]

Aliases

Each pattern-matching function has an alias whose name begins with REGEX_.

REGEX_CONTAINS() is an alias for REGEXP_CONTAINS().
REGEX_LIKE() is an alias for REGEXP_LIKE().
REGEX_POSITION() is an alias for REGEXP_POSITION().
REGEX_REPLACE() is an alias for REGEXP_REPLACE().