How to convert a string to Title Case in TIBCO Spotfire client

Product:TIBCO Spotfire Analyst
Versions:All Versions

Summary:
This article describes how to convert a string to Title Case using a regular expression in TIBCO Spotfire client.

Details:
There is no built in function for 'title case' like there is with Upper() and Lower(), but it is still possible to perform this string conversion using Spotfire Expressions. Title case, also known as "headline style" and "capital case", is when all words are capitalized, except for certain subsets defined by rules that are not universally standardized. The standardization is only at the level of house styles and individual style manuals. A simplified variant is start case where all words, including articles, prepositions and conjunctions, start with a capital letter.

For example:

• "The Quick Brown Fox Jumps Over The Lazy Dog."

or

• "The Quick Brown Fox Jumps Over the Lazy Dog."

Resolution:
These expressions replace any lower case letter either at the beginning of a string or after a space and converts it to upper case. You can optionally add another RXReplace() function so that non-principal words are not capitalized.

Title Case - All Words:

RXReplace([myString],"(^[a-z]| [a-z])","\\U$1","g")

Explanation:

• ^ - Matches start of line
• [a-z] - Matches any lower case letter
• | - Is an OR operator so we match either a letter at the start of the string OR a letter after a space
• \\U - Is the escaped form of \U which returns the upper case form of the subsequent values
• $1 - Returns the values matched in the parenthesis in the search

Title Case - Principal Words Only

RXReplace(
RXReplace([myString],"(^[a-z]| [a-z])","\\U$1","g"),
"(?!^)(A|An|The|This|And|But|Or|For|Nor|At|On|In|For|Since) ","\\L$1 ","g")

Note: the list of words to NOT be capitalized is located in the 3rd line of the above expression and can be edited as required.

Explanation:

• (?!^) - Is a 'negative lookahead' which means it won't match the start of the string (so the first word is always capitalized)
• (A|An|The|This|And|But|Or|For|Nor|At|On|In|For|Since) - This is the list of words to not be capitalized
• \\L - Is the escaped form of \L which returns the lower case form of the subsequent values

Optional:

If the string to be converted is all capitals, then the above expressions will not work since they look for lower case letters to capitalize. In this case, you can use the Lower() function to convert those to lower case first and then the title case conversion will continue as expected:

RXReplace(Lower([myString]),"(^[a-z]| [a-z])","\\U$1","g")

or

RXReplace(
RXReplace(Lower([myString]),"(^[a-z]| [a-z])","\\U$1","g"),
"(?!^)(A|An|The|This|And|But|Or|For|Nor|At|On|In|For|Since) ","\\L$1 ","g")