28. Get a Contact's First Name in Camel Case

Note: The scenario below uses data from the DESelect Demo Data Package.

Scenario: You want to get the First Name of your Contacts in Camel Case for your email campaign. In order to achieve that, you want to convert only the first character of each record to uppercase, and the rest to lowercase.

Note: This scenario uses the feature SQL functions, which is available in DESelect Advanced.

1. Selection Criteria

On the selections overview screen, click on New to create a new selection. 

Drag DESELECT_DEMO_Contacts to the Selected Data Extensions section.

2. Target Definition

Hit the Create Data Extension button, enter a name, eg. Camel Case Contacts, and press Save.

Select all the fields by clicking on the Add All Fields button.

Click Save Data Extension.

Click on the Delete icon next to the First Name field that's mapped to First Name.

Now we're going to add a Custom Value:

• Under Custom Values on the left, click Add new value.
• Under Name, enter: First Name Camel Case and hit Next.
• Choose Type: Apply formula to a field.
• Choose Type: Apply any function.
• On the drop-down list on the top with the label, Field Type choose Text.
• On the drop-down list on the Insert Function section select CONCAT and click on the Insert Formula button. CONCAT will be used to combine the first uppercase letter with the rest of the lowercase string.
• Remove all characters between string2 and the last parenthesis.
• On the inserted function remove string1 and insert the UPPER function the same way you inserted CONCAT. Upper is used to convert a string to uppercase. Your formula should now look like this: CONCAT(UPPER(string), string2).
• Remove the string parameter of UPPER and between the parentheses insert the LEFT function. LEFT is used to extract only a specific amount of characters out of the whole string, starting at the beginning of the string. Your formula should now look like this: CONCAT(UPPER(LEFT(string, number_of_chars)), string2)
• Replace the number_of_chars parameter with the number 1 to only extract the first character.
• Remove the string parameter from the LEFT function.
• On the first drop-down list on the Insert Field section with the label Data Extension select DESELECT_DEMO_Contacts.
• On the second drop-down list on the Insert Field section with the label Field select the First Name.
• Click on the Insert Field button. Your formula should now look like this: CONCAT(UPPER(LEFT("DESELECT_DEMO_Contacts"."First Name", 1)), string2)
• Replace the string2 parameter of CONCAT with the function LOWER. LOWER is used to convert all the characters of the string to lowercase, apart from the first one.
• Replace the string parameter of LOWER with the function SUBSTRING. SUBSTRING is used to extract all the characters of the string apart from the first one. Your formula should now look like this: CONCAT(UPPER(LEFT("DESELECT_DEMO_Contacts"."First Name", 1)), LOWER(SUBSTRING(string, start, length)))
• Replace the string parameter of SUBSTRING with the First Name field.
• Replace the start parameter with the number 2.
• Replace the length parameter with the LEN function. LEN is used to get the total number of characters of the string. Your formula should now look like this: CONCAT(UPPER(LEFT("DESELECT_DEMO_Contacts"."First Name", 1)), LOWER(SUBSTRING("DESELECT_DEMO_Contacts"."First Name", 2, LEN(string))))
• Replace string parameter of LEN function with First Name field. Your formula should now look like this: CONCAT(UPPER(LEFT("DESELECT_DEMO_Contacts"."First Name", 1)), LOWER(SUBSTRING("DESELECT_DEMO_Contacts"."First Name", 2, LEN("DESELECT_DEMO_Contacts"."First Name"))))
• Hit Save.

The logic defined above will, for each row, convert the value of the First Name column to Camel Case.

Now that the custom value has been created, we can map it to the field First Name by drag-and-dropping it from the Custom Values to the First Name mapping field.

3. Preview

When you click Run Preview on the Preview screen, a results table will be shown. All records in the First Name column will be in Camel Case.