---
schema_version: "1"
id: 160e132db9e6ed09a999b72773111ddb27b804fe274989f7f659d79a2e2edc0b
title: Additional Functions (Regex, Hadoop Hive, Google Big Query)
collection: tableau
version: current
locale: en-us
portal_url: https://docs.salesforce.com/tableau/current/en-us/pro/desktop/functions_functions_additional.md
canonical_url: https://help.tableau.com/current/pro/desktop/en-us/functions_functions_additional.htm
source_site: help.tableau.com
content_type: documentation
content_hash: 4f5349024ce1a637aa72babdf225ae52b6b68423b33c3f5fc63bae36aa5170a0
description: Reference for additional functions including regular expressions, Hadoop Hive, and BigQuery.
---
[Skip to main content](#main-content)

# Additional Functions (Regex, Hadoop Hive, Google Big Query)See also:

[Pass-Through Functions (RAWSQL)](https://help.tableau.com/current/pro/desktop/en-us/functions_functions_passthrough.htm).

## Regular Expressions 

For information on regular expression syntax, see your data source's documentation. For Tableau extracts, regular expression syntax conforms to the standards of the ICU (International Components for Unicode), an open source project of mature C/C++ and Java libraries for Unicode support, software internationalization, and software globalization. See the [Regular Expressions](http://userguide.icu-project.org/strings/regexp) page in the online ICU User Guide.

### REGEXP\_REPLACE

<table class="table--plain" style="width: 100%;"><colgroup><col style="width: 20%;"><col></colgroup><tbody><tr><td>Syntax</td><td><code>REGEXP_REPLACE(string, pattern, replacement)</code></td></tr><tr><td>Output</td><td>String</td></tr><tr><td>Definition</td><td>Returns a copy of the given string where the regular expression pattern is replaced by the replacement string. </td></tr><tr><td>Example</td><td><pre>REGEXP_REPLACE('abc 123', '\s', '-') = 'abc-123'</pre></td></tr><tr><td>Notes</td><td><p>This function is available for Text File, Hadoop Hive, Google BigQuery, PostgreSQL, Tableau Data Extract, Microsoft Excel, Salesforce, Vertica, Pivotal Greenplum, Teradata (version 14.1 and above), Snowflake, and Oracle data sources. </p><p>For Tableau data extracts, the pattern and the replacement must be constants. </p></td></tr></tbody></table>

### REGEXP\_MATCH

<table class="table--plain" style="width: 100%;"><colgroup><col style="width: 20%;"><col></colgroup><tbody><tr><td>Syntax</td><td><code>REGEXP_MATCH(string, pattern)</code></td></tr><tr><td>Output</td><td>Boolean</td></tr><tr><td>Definition</td><td>Returns true if a substring of the specified string matches the regular expression pattern.</td></tr><tr><td>Example</td><td><pre>REGEXP_MATCH('-([1234].[The.Market])-','\[\s*(\w*\.)(\w*\s*\])')=true</pre></td></tr><tr><td>Notes</td><td><p> This function is available for Text File, Google BigQuery, PostgreSQL, Tableau Data Extract, Microsoft Excel, Salesforce, Vertica, Pivotal Greenplum, Teradata (version 14.1 and above), Impala 2.3.0 (through Cloudera Hadoop data sources), Snowflake, and Oracle data sources. </p><p>For Tableau data extracts, the pattern must be a constant. </p></td></tr></tbody></table>

### REGEXP\_EXTRACT

<table class="table--plain" style="width: 100%;"><colgroup><col style="width: 20%;"><col></colgroup><tbody><tr><td>Syntax</td><td><code>REGEXP_EXTRACT(string, pattern)</code></td></tr><tr><td>Output</td><td>String</td></tr><tr><td>Definition</td><td>Returns the portion of the string that matches the regular expression pattern. </td></tr><tr><td>Example</td><td><pre>REGEXP_EXTRACT('abc 123', '[a-z]+\s+(\d+)') = '123'</pre></td></tr><tr><td>Notes</td><td><p> This function is available for Text File, Hadoop Hive, Google BigQuery, PostgreSQL, Tableau Data Extract, Microsoft Excel, Salesforce, Vertica, Pivotal Greenplum, Teradata (version 14.1 and above), Snowflake, and Oracle data sources. </p><p>For Tableau data extracts, the pattern must be a constant. </p></td></tr></tbody></table>

### REGEXP\_EXTRACT\_NTH

<table class="table--plain" style="width: 100%;"><colgroup><col style="width: 20%;"><col></colgroup><tbody><tr><td>Syntax</td><td><code>REGEXP_EXTRACT_NTH(string, pattern, index)</code></td></tr><tr><td>Output</td><td>String</td></tr><tr><td>Definition</td><td>Returns the portion of the string that matches the regular expression pattern. The substring is matched to the nth capturing group, where n is the given index. If index is 0, the entire string is returned. </td></tr><tr><td>Example</td><td><pre>REGEXP_EXTRACT_NTH('abc 123', '([a-z]+)\s+(\d+)', 2) = '123'</pre></td></tr><tr><td>Notes</td><td><p>This function is available for Text File, PostgreSQL, Tableau Data Extract, Microsoft Excel, Salesforce, Vertica, Pivotal Greenplum, Teradata (version 14.1 and above), and Oracle data sources.</p><p>For Tableau data extracts, the pattern must be a constant. </p></td></tr></tbody></table>

## Hadoop Hive Specific Functions

**Note**: Only the PARSE\_URL and PARSE\_URL\_QUERY functions are available for Cloudera Impala data sources.

### GET\_JSON\_OBJECT(JSON string, JSON path)

Returns the JSON object within the JSON string based on the JSON path.

### PARSE\_URL(string, url\_part)

Returns a component of the given URL string where the component is defined by url\_part. Valid url\_part values include: 'HOST', 'PATH', 'QUERY', 'REF', 'PROTOCOL', 'AUTHORITY', 'FILE' and 'USERINFO'.

Example

`PARSE_URL('http://www.tableau.com', 'HOST') = 'www.tableau.com'`

### PARSE\_URL\_QUERY(string, key)

Returns the value of the specified query parameter in the given URL string. The query parameter is defined by the key.

Example

`PARSE_URL_QUERY('http://www.tableau.com?page=1&cat=4', 'page') = '1'`

### XPATH\_BOOLEAN(XML string, XPath expression string)

Returns true if the XPath expression matches a node or evaluates to true.

Example

`XPATH_BOOLEAN('<values> <value id="0">1</value><value id="1">5</value>', 'values/value[@id="1"] = 5') = true`

### XPATH\_DOUBLE(XML string, XPath expression string)

Returns the floating-point value of the XPath expression.

Example

`XPATH_DOUBLE('<values><value>1.0</value><value>5.5</value> </values>', 'sum(value/*)') = 6.5`

### XPATH\_FLOAT(XML string, XPath expression string)

Returns the floating-point value of the XPath expression.

Example

`XPATH_FLOAT('<values><value>1.0</value><value>5.5</value> </values>','sum(value/*)') = 6.5`

### XPATH\_INT(XML string, XPath expression string)

Returns the numerical value of the XPath expression, or zero if the XPath expression cannot evaluate to a number.

Example

`XPATH_INT('<values><value>1</value><value>5</value> </values>','sum(value/*)') = 6`

### XPATH\_LONG(XML string, XPath expression string)

Returns the numerical value of the XPath expression, or zero if the XPath expression cannot evaluate to a number.

Example

`XPATH_LONG('<values><value>1</value><value>5</value> </values>','sum(value/*)') = 6`

### XPATH\_SHORT(XML string, XPath expression string)

Returns the numerical value of the XPath expression, or zero if the XPath expression cannot evaluate to a number.

Example

`XPATH_SHORT('<values><value>1</value><value>5</value> </values>','sum(value/*)') = 6`

### XPATH\_STRING(XML string, XPath expression string)

Returns the text of the first matching node.

Example

`XPATH_STRING('<sites ><url domain="org">http://www.w3.org</url> <url domain="com">http://www.tableau.com</url></sites>', 'sites/url[@domain="com"]') = 'http://www.tableau.com'`

## Google BigQuery Specific Functions

### DOMAIN(string\_url)

Given a URL string, returns the domain as a string.

Example

`DOMAIN('http://www.google.com:80/index.html') = 'google.com'`

### GROUP\_CONCAT(expression)

Concatenates values from each record into a single comma-delimited string. This function acts like a SUM() for strings.

Example

`GROUP_CONCAT(Region) = "Central,East,West"`

### HOST(string\_url)

Given a URL string, returns the host name as a string.

Example

`HOST('http://www.google.com:80/index.html') = 'www.google.com:80'`

### LOG2(number)

Returns the logarithm base 2 of a number.

Example

`LOG2(16) = '4.00'`

### LTRIM\_THIS(string, string)

Returns the first string with any leading occurrence of the second string removed.

Example

`LTRIM_THIS('[-Sales-]','[-') = 'Sales-]'`

### RTRIM\_THIS(string, string)

Returns the first string with any trailing occurrence of the second string removed.

Example

`RTRIM_THIS('[-Market-]','-]') = '[-Market'`

### TIMESTAMP\_TO\_USEC(expression)

Converts a TIMESTAMP data type to a UNIX timestamp in microseconds.

Example

`TIMESTAMP_TO_USEC(#2012-10-01 01:02:03#)=1349053323000000`

### USEC\_TO\_TIMESTAMP(expression)

Converts a UNIX timestamp in microsseconds to a TIMESTAMP data type.

Example

`USEC_TO_TIMESTAMP(1349053323000000) = #2012-10-01 01:02:03#`

### TLD(string\_url)

Given a URL string, returns the top level domain plus any country domain in the URL.

Example

`TLD('http://www.google.com:80/index.html') = '.com'`

`TLD('http://www.google.co.uk:80/index.html') = '.co.uk'`