Regular Expression Replacement Returns Unexpected Results When Using Pattern '.*'

KNOWLEDGE BASE

Regular Expression Replacement Returns Unexpected Results When Using Pattern '.*'

Published: 06 Sep 2023
Last Modified Date: 08 Sep 2023

Issue

The results of the calculation using the REGEXP_REPLACE function are expected to be [0] instead, the result is [00] when the characters are ".*".

REGEXP_REPLACE([String],'.*','0')
User-added image

Environment

Tableau Prep Builder
Tableau Desktop
Regular Expression

Resolution

Please consider using the below patterns to make replacements.

Option 1

Replace '. *' with '. +'.
Sample calculation:

REGEXP_REPLACE([String],'.+', "0")

Option 2

Add a mark for the beginning of the line and change it to '^. *'.
Sample calculation:

REGEXP_REPLACE([String],'^.*', "0")

Please also check the packaged workbook located in the Attachments section of this article for an example.

Cause

This is the expected behavior for regular expressions.

You can check any other regular expression tester online, and the results will be the same as Tableau.