I wrote a regular expression in the hope that I would be able to replace every match (that is just one character) with an uppercase character. This is a very long text file, more like a feedback and flight delay description. This would take a very long time to do by hand, and mostly it comes from a Qualtrics tool, so we can't change it through the backend.
It would be quite complicated to do without regular expressions because current examples that are floating right now have performance issues, which I definitely would like to avoid.
Current Solution 01: [Delay Desc Ref] is my current Field
REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(
REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(
REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(
REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(
REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(
REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(
REGEXP_REPLACE(REGEXP_REPLACE(
[Delay Desc Ref]
,'(?<=^|\s)a','A'),'(?<=^|\s)b','B'),'(?<=^|\s)c','C'),'(?<=^|\s)d','D')
,'(?<=^|\s)e','E'),'(?<=^|\s)f','F'),'(?<=^|\s)g','G'),'(?<=^|\s)h','H')
,'(?<=^|\s)i','I'),'(?<=^|\s)j','J'),'(?<=^|\s)k','K'),'(?<=^|\s)l','L')
,'(?<=^|\s)m','M'),'(?<=^|\s)n','N'),'(?<=^|\s)o','O'),'(?<=^|\s)p','P')
,'(?<=^|\s)q','Q'),'(?<=^|\s)r','R'),'(?<=^|\s)s','S'),'(?<=^|\s)t','T')
,'(?<=^|\s)u','U'),'(?<=^|\s)v','V'),'(?<=^|\s)w','W'),'(?<=^|\s)x','X')
,'(?<=^|\s)y','Y'),'(?<=^|\s)z','Z')
Solution 02:
TRIM(
upper(left(SPLIT([Delay Desc Ref], " ", 1),1)) + lower(right(SPLIT([Delay Desc Ref], " ", 1),(len(SPLIT([Delay Desc Ref], " ", 1))-1))) + " "
+
IFNULL(upper(left(SPLIT([Delay Desc Ref], " ", 2),1)),"") + ifnull(lower(right(SPLIT([Delay Desc Ref], " ", 2),(len(SPLIT([Delay Desc Ref], " ", 2))-1))),"")
+ " " +
IFNULL(upper(left(SPLIT([Delay Desc Ref], " ", 3),1)),"") + ifnull(lower(right(SPLIT([Delay Desc Ref], " ", 3),(len(SPLIT([Delay Desc Ref], " ", 3))-1))),"")
+ " " +
IFNULL(upper(left(SPLIT([Delay Desc Ref], " ", 4),1)),"") + ifnull(lower(right(SPLIT([Delay Desc Ref], " ", 4),(len(SPLIT([Delay Desc Ref], " ", 4))-1))),"")
+ " " +
IFNULL(upper(left(SPLIT([Delay Desc Ref], " ", 5),1)),"") + ifnull(lower(right(SPLIT([Delay Desc Ref], " ", 5),(len(SPLIT([Delay Desc Ref], " ", 5))-1))),"")
+ " " +
IFNULL(upper(left(SPLIT([Delay Desc Ref], " ", 6),1)),"") + ifnull(lower(right(SPLIT([Delay Desc Ref], " ", 6),(len(SPLIT([Delay Desc Ref], " ", 6))-1))),"")
+ " " +
IFNULL(upper(left(SPLIT([Delay Desc Ref], " ", 7),1)),"") + ifnull(lower(right(SPLIT([Delay Desc Ref], " ", 7),(len(SPLIT([Delay Desc Ref], " ", 7))-1))),"")
)
All the above mentioned codes are working fine, but there is some performance issue, so I was trying to solve the issue using regular expressions.
Performance Issue Image using workbook optimiser
I was able to generate the desired outcome using regex . Please refer to the below image
But the same logic does not work when using the "Regexp_Replace" function. I tried using both Live and Extract Connection . I am attaching the screenshot for your reference.
Actual Tableau Expression : REGEXP_REPLACE([Delay Desc Ref],"\b(\w)","\U\1\E")
Example :
Delay Desc Ref Attributes
aircraft defects aircraft rotation airport facilities baggage processing ciq (customs, immigration & quarantine) clearance /sequencing for arrival clearance en-route (atfm)
Kindly suggest what I am doing wrong and how I can achieve the same within Tableau.
Search: \b(\w)
Replace by: \U\1\E