Tuesday, 15 January 2013 08:41

When the propercase function just doesn't cut it

Written by
Rate this item
(0 votes)

Demo

The propercase function is Crystal's equivalent of the TitleCase function in MS Word. Traditionally used for the titles of everything (books, plays, movies, etc), title case returns a capital/uppercase letter for the start of every significant word – where words like and, of, the and a are not counted as significant.

 

Well, there are situations where the standard Propercase function in Crystal Reports just does not cut it. Here are some issues reported by a client some time ago when using the standard propercase function to clean up data report-side:

42829C: Beneficiary name has John W Howes III; function translates to John W Howes Iii.
33881C: Address line 1 has 901 Thompson Cir NW; function translates to 901 Thompson Cir Nw.
40720C: Beneficiary name has Harry H Hill III Trust, function translates to Harry H Hill Iii Trust
59486C: Employee last name is McLuen function translates to Mcluen.
71705C: Address line 1 has 240 Watson Dr NW; function translates to 240 Watson Dr Nw.

To resolve this issue, I created a custom propercase function that took into consideration a number of exceptions and custom data patterns.

Function (stringVar InputString)

// **************************************************************
// Modified: Nov-04-2009: expanded list of Uppercase words and
// added logic for 'Mc' names.
// Modified: Nov-13-2009: replaced all commas in names with spaces for parsing 
// **************************************************************


// Purpose: This formula takes an input string and returns the
// the propercase conversion of the string. For example, an
// input string of "c/o rosemont seminary" returns "C/O Rosemount
// Seminary". This custom formula fixes the problem with the regular
// propercase function where letters following apostrophes (') are
// returned in uppercase instead of lowercase (for example instead of returning
// St Mary's, the propercase function returns St. Mary'S.

// declare array to capture distinct words in input string
local stringvar array ArrayofWordsinInputString;

// declare array to hold a list of odd characters that
// cause problems in converting to propercase
local stringvar array ArrayofOddCharacters;

// declare array to hold a list of conjunctions that
// should NOT be capitalized - should be left unchanged
local stringvar array ArrayofWordsUnchanged;

// declare array to hold a list of names that have two-letter abbreviations
// at the beginning of the name (like mcluen, mcdonalds) that need to be conditionally
// uppercased to McLuen, McDonalds (i.e. the name after the abbreviation needs to be in
// uppercase
local stringvar array ArrayofMcNames;

// declare array to hold a list of words that
// should always be capitalized
local stringvar array ArrayofWordsUpperCase;

// declare variable to count number of words in input string
local numbervar nWordCount;
// declare a counter variable

local numbervar i;
// declare variable for holding cleaned-up string
local stringvar sOutputString := "";

// declare variable to hold current word in the input string
local stringvar CurrentWord;

// declare variable to hold first two letters in current word in the input string
local stringvar CurrentWordFirstTwoLetters;

// declare variable to hold the number of characters in current word in the input string
local numbervar nWordLen;
// declare variable to hold proper case word after conversion
local stringvar ProperCaseWord;
// declare variable to hold current letter in current word
local stringvar CurrentLetter;
// declare boolean variable to flag if current character in string is
// an odd character (/, ., \ etc)
local booleanvar blnOddChar := false;

// split up the input field into an array of individual words
ArrayofWordsinInputString := split(replace(InputString,',',' '));

// get count of number of discrete words in input string
nWordCount := ubound(ArrayofWordsinInputString);

// assign odd characters to array;
ArrayofOddCharacters := ["." , "/", "\", "-"];

// assign UpperCase characters to array;
ArrayofWordsUpperCase := ['PO', 'III', 'I', 'II', 'IV', 'V', 'VI', 'VIII', 'IX', 'X', 'NW', 'NE', 'SE', 'SW'];

// assign names to McNames array
ArrayofMcNames := ['mc'];

// assign words that should NOT be propercased in input string
ArrayofWordsUnchanged := ["and", "of", "the", "for"];

// ********** Begin Looping through Input String ***********

// loop through each word in input string
// referred to as OUTER LOOP
For i := 1 to nWordCount do
(
// capture the current word in variable
CurrentWord := lcase(ArrayofWordsinInputString[i]);
// figure out length of current word
nWordLen := length(CurrentWord);
// initialize variable - clear out
ProperCaseWord := "";
// initialize boolean variable - set to false
blnOddChar := false;

// get the first two letters in the current word;
if nWordLen > 2
then CurrentWordFirstTwoLetters := left(CurrentWord, 2)
else CurrentWordFirstTwoLetters := "";

// declare counter variable
local numbervar j;

// The following if statement decides whether to enter
// INNER LOOP depending on whether the current word
// being processed belongs to the list words that should
// be left unchanged. For example, if the inputstring is
// "the diocese of st. mary", the word "of" that belongs to
// our list (see array "ArrayofWordsUnchanged") would not
// be propercased. So the result should be "The Diocese of
// St. Mary".
// NOTE: This rule applies only if the word is in the
// second or subsequent word in the field. If it's the first
// word in the field, it will be propercased. For example, "diocese
// of mexico" will be "Diocese of Mexico", whereas "the diocese
// of mexico" will be "The Diocese of Mexico".

// if the current word is in the list of words
// to be presented always in UpperCase,
// then simple convert to Uppercase and pass to ProperCaseWord
if CurrentWord in ArrayofWordsUpperCase
then ProperCaseWord := ucase(CurrentWord)

// check if names in the McNames array
else if ucase(CurrentWordFirstTwoLetters) in ArrayofMcNames
then ProperCaseWord := ucase(CurrentWord[1]) & lcase(CurrentWord[2]) & ucase(CurrentWord[3]) & lcase(right(CurrentWord, nWordLen - 3))

else if (i > 1 and CurrentWord in ArrayofWordsUnchanged)
// simply pass CurrentWord into Propercase variable
// this will avoid going through the INNER LOOP and
// will not uppercase this word.
then ProperCaseWord := CurrentWord
else
(
// loop through each letter in the current word
// referred to as INNER LOOP
For j := 1 to nWordLen do
(
// begin looping through letters

// if this is the first character in the current word
if j = 1
then
(
// if IT IS an odd character
if CurrentWord[j] in ArrayofOddCharacters
then
(
// reverse boolean variable to indicate that this character is odd
// so that the following character will be forced to uppercase
blnOddChar := true;
// then do nothing to the current character - simply append it to ProperCaseWord
ProperCaseWord := ProperCaseWord & CurrentWord[j];
)
else // then if IT IS NOT an odd character
(
// convert this first character to uppercase
ProperCaseWord := ProperCaseWord & ucase(CurrentWord[j])
)
) // end if statement j = 1

// if this is the second or subsequent characters in the current word
else if j > 1
then
(
// if IT IS an odd character
if CurrentWord[j] in ArrayofOddCharacters
then
(
// reverse boolean variable to indicate that this character is odd
// so that the following character will be forced to uppercase
blnOddChar := true;
// then do nothing to the current character - simply append it to ProperCaseWord
ProperCaseWord := ProperCaseWord & CurrentWord[j];
)
else // then if IT IS NOT an odd character
(
// if the previous character was an odd character
if blnOddChar = true
then // this character (letter) will be converted to uppercase
(
blnOddChar := false;
ProperCaseWord := ProperCaseWord & ucase(CurrentWord[j]);
)

// else if the previous character was NOT an odd character,
else
// then this character (letter) will be converted to lowercase
(
ProperCaseWord := ProperCaseWord & lcase(CurrentWord[j]);
)
); // end if statement
) // end if statement j > 1

); // end INNER LOOP

ProperCaseWord;

); // end if statement - if CurrentWord ArrayofWordsUnchanged

// append the propercased word to the output string in outer loop

// Add space to word being processed in
// OUTER LOOP is more than one character long
sOutputString := sOutputString + ProperCaseWord & " "


); // End OUTER LOOP

// print clean-up string
sOutputString;

trim(sOutputString)
Read 10017 times Last modified on Wednesday, 22 April 2015 09:08
Will Munji

Will Munji is a seasoned data integration, data warehousing and business intelligence (BI) architect & developer who has been working in the DW/BI space for a while. He got his start in BI working on Brio SQR (later Hyperion SQR) and the Crystal Decisions stack (Reports, Analysis & Enterprise) and SAP BusinessObjects / Microsoft BI stacks. He currently focuses on Talend Data Management Suite, Hadoop, SAP BusinessObjects BI stack as well as Jaspersoft and Tableau. He has consulted for many organizations across a variety of industries including healthcare, manufacturing, retail, insurance and banking. At Kindle Consulting, Will delivers DW/BI/Data Integration solutions that range from front-end BI development (dashboards, reports, cube development, T-SQL/ PL/SQL ...) to data services (ETL/ DI development), data warehouse architecture and development, data integration to BI Architecture design and deployment.

Leave a comment

Make sure you enter all the required information, indicated by an asterisk (*). HTML code is not allowed.

Contact Us

Kindle Consulting, Inc

6595 Roswell Road 
Suite G2025 
Atlanta, GA 30328

Phone: 404.551.5494
Fax: 404.551.5452
Email: info@kindleconsulting.com

Talend Gold Partner