regex extract tableau

1
votes

I am trying to create a regex extract function in tableau. When I use the regex match function, everything works, but the same formula for extract doesn't work. Thoughts? 

REGEXP_MATCH([Comments Keyed In By User],
             '.*(|SC)( |)([0-9][0-9][0-9])( - | | -|-|)([0-9][0-9]|).*')

vs 

REGEXP_EXTRACT([Comments Keyed In By User],
               '.*(|SC)( |)([0-9][0-9][0-9])( - | | -|-|)([0-9][0-9]|).*')

Thanks

1
regextableau-api
What do you mean by "does not work"? What are sample texts you need to match/extract? – Wiktor Stribiżew
It doesn't extract any data at all. The column is blank. I need to extract data like SC919 or SC-934 or SC 434 in a long sentence od data – Sanch
Try REGEXP_EXTRACT([Comments Keyed In By User], 'SC[- ]*[0-9]{3}') (or the regex can also have word boundaries - '\bSC[- ]*\d{3}\b') – Wiktor Stribiżew
Does that work for you? – Wiktor Stribiżew
No. I am getting only null values in tableau – Sanch

1 Answers

2
votes

You do not need the .* around the pattern when you extract the matches, and it seems Tableau requires the capturing group to be defined in the pattern to actually get the text into the results.

Use

REGEXP_EXTRACT([Comments Keyed In By User], '(SC[- ]*[0-9]{3})')

Details:

  • SC - matches SC substring
  • [- ]* - zero or more - or spaces (replace * with ? to match one or zero occurrences)
  • [0-9]{3} - 3 digits

To match whole words only, enclose the pattern with word boundaries \b: '\b(SC[- ]*[0-9]{3})\b'.