Using Regex to extract numeric values in Tableau

Question

I am trying to pull out the numeric values (10004, 12245, 13456) from the following IDs:

10004a, 12v245, and 13456n

I can get the correct ID numbers with the exception of 12v245 ID, using the following regex code:

REGEXP_EXTRACT([ID], '([0-9]+)')

The 12v245 ID is only returning the the first two numbers. What am I missing in my code?

Answer 1

Your issue is that the function REGEXP_EXTRACT in Tableau requires exactly one capturing group.

The function [0-9]+ returns a capturing group per block of numbers and as the ID 12v245 has a letter in between the string of numbers it returns two capturing groups i.e. the 12 and then the 245.

The workaround for this is to use a nested replace as follows:

REGEXP_REPLACE(
                REGEXP_REPLACE(
                    REGEXP_REPLACE([ID], '[\D]+',"")
                , '[\D]+' , "")
            , '[\D]+' , "")

Depending on the nature of your data you may want to add more replaces.

This issue is documented on the Tableau community so feel free to vote up for a better fix: https://community.tableau.com/ideas/4975#