Google Cloud Spanner Regular Expression Functions

  • Post author:
  • Post last modified:July 13, 2020
  • Post category:General
  • Reading time:6 mins read

The regular expressions are commonly used to identify the precise pattern of the characters in the string. Many popular programming languages such as Python, Java, R, etc. extensively use regular expression functions. You can also use regular expression to validate strings, for example, extracting numbers from the string values. In this article, we will check regular expression functions supported in cloud spanner.

Cloud Spanner Regular Expression Functions

The regular expression functions in cloud spanner are string functions that match a given regular expression. These functions are commonly called as a ‘regex’ functions.

Syntax and usage is similar to other relational database such as Snowflake, Redshift, Netezza, etc.

Following are some of the regular expression functions that google cloud Spanner relational database supports.

Now let us check these functions in brief.

Cloud Spanner REGEXP_CONTAINS Function

The cloud Spanner REGEXP_CONTAINS string function returns TRUE if input string is a partial match for the given regular expression.

Following is the syntax of the cloud Spanner REGEXP_CONTAINS Function.

REGEXP_CONTAINS(value, regexp)

Where value is the input string and regexp is the regular expression to search.

Following is the example to demonstrate the REGEXP_CONTAINS Function.

SELECT
  email,
  REGEXP_CONTAINS(email, r"@[a-zA-Z0-9-]+\.[a-zA-Z0-9-.]+") AS is_valid
FROM
  (SELECT
    ["foo1@example.com", "foo2@example.org", "www.foo.net"]
    AS addresses),
  UNNEST(addresses) AS email;

+-----------------+----------+
| email           | is_valid |
+-----------------+----------+
| foo1@example.com | true     |
| foo2@example.org | true     |
| www.foo.net      | false    |
+-----------------+----------+

Cloud Spanner REGEXP_EXTRACT Function

The cloud Spanner REGEXP_EXTRACT string function returns the first substring in input string that matches the given regular expression. The function will returns NULL if there is no match.

Following is the syntax of the cloud Spanner REGEXP_EXTRACT function.

REGEXP_EXTRACT(value, regexp)

Where value is the input string and regexp is the regular expression to search.

Following is the example to demonstrate the REGEXP_CONTAINS function.

WITH email_addresses AS
  (SELECT "foo1@example.com" as email
  UNION ALL
  SELECT "foo2@example.org" as email
  UNION ALL
  SELECT "foo3@example.net" as email)

SELECT
  REGEXP_EXTRACT(email, r"^[a-zA-Z0-9_.+-]+")
  AS user_name
FROM email_addresses;

+-----------+
| user_name |
+-----------+
| foo1      |
| foo1      |
| foo1      |
+-----------+

Cloud Spanner REGEXP_EXTRACT_ALL Function

The cloud Spanner REGEXP_EXTRACT_ALL string function returns an array of all substring in the input string that matches the given regular expression. The function will returns NULL if there is no match.

Following is the syntax of the cloud Spanner REGEXP_EXTRACT_ALL function.

REGEXP_EXTRACT_ALL(value, regexp)

Where value is the input string and regexp is the regular expression to search.

Following is the example to demonstrate the REGEXP_EXTRACT_ALL function.

WITH code_markdown AS
  (SELECT "Try `function(x)` or `function(y)`" as code)

SELECT
  REGEXP_EXTRACT_ALL(code, "`(.+?)`") AS example
FROM code_markdown;

+----------------------------+
| example                    |
+----------------------------+
| [function(x), function(y)] |
+----------------------------+

Cloud Spanner REGEXP_REPLACE Function

The cloud Spanner REGEXP_REPLACE function returns the string by replacing specified pattern. If no matches found, original string will be returned.

Following is the syntax of the cloud Spanner REGEXP_REPLACE function.

REGEXP_REPLACE(value, regexp, replacement)

Where value is the input string and regexp is the regular expression to search.

Following is the example to demonstrate the REGEXP_REPLACE function.

WITH remove_hash AS
  (SELECT "# This is comment" as comment
  UNION ALL
  SELECT "# Another heading" as comment )

SELECT
  REGEXP_REPLACE(comment, r"^#", "--")
  AS html
FROM remove_hash;

+--------------------------+
| html                     |
+--------------------------+
| -- This is comment        |
| -- Another comment |
+--------------------------+

Hope this helps 🙂

Tags: , ,