Take control over strings with built-in SQL functions. Let the query do the job.
Salesforce Marketing Cloud SQL implementation does not support user-defined functions. There are, however, multiple built-in functions that are useful on a day-to-day basis when working with queries.
Here, I will cover only selected string functions, that I find most useful for Marketing Automation purposes. They will help you change the values, split and concatenate them, or find elements within.
You Should Know
RIGHT functions allow you to cut down the string to just part of it.
Both require two parameters - source value and the number of characters to return.
SELECT LEFT(FirstName, 1) AS FirstNameInitialLetter , RIGHT(FirstName, 1) AS FirstNameEndingLetter , LEFT(s.DateJoined, 3) AS MonthJoined
Both those functions can be beneficial to clean and streamline your data by themselves, but their real power becomes evident when you mix them with
CHARINDEX for content-aware cuts.
LEN function returns a count of characters in the value.
SELECT SubscriberKeyFROM _SubscribersWHERE LEN(SubscriberKey) != 18
It is less useful for segmentation but awesome for various administrative purposes. Apart from the example above, I highly recommend checking how this function combines with
CHARINDEX function lets you find if and where one string is in another. You pass the value you want to find, the value in which you wish to search and, optionally, the index from which you want to start the search.
CHARINDEX("Manager", JobTitle, 1)
In the example above, I used the optional starting index, but in most cases, you will not be using it. The
CHARINDEX defaults to starting from the beginning so we can write the above line shorter as
The function returns one three possible values:
- NULL - if either value to find or searched value is NULL
- 0 - if the value to find is not within the searched value
- any other digit - the index where the value to find starts within the searched value - if there are multiple occurrences, the result will show the starting index of just the first one.
CHARINDEX in SFMC is case-insensitive in its searches. You can, however, enforce case-sensitivity by adding
CHARINDEX("Manager", JobTitle COLLATE Latin1_General_CS_AS)
You Should Know
SELECT CASE WHEN CHARINDEX(' ', InstitutionName) > 0 THEN LEFT(InstitutionName, CHARINDEX(' ', InstitutionName) - 1) ELSE InstitutionName END AS InstitutionTypeFROM Accounts
The above query checks whether there is a space within
InstitutionName value and if yes - it captures only the first word. Think about all the educational institution names with
School of... - by taking only the first part, we can create new simplified data point categorizing records by type.
CONCAT function allows you to join two or more strings together.
You can use it on both existing values pulled from source data extension, as well as on custom strings and digits added directly in the query. In most cases, you will mix both.
SELECT SubscriberKey , EmailAddress , CONCAT(FirstName, ' ', LastName) AS FullName
You can concatenate any number of strings by just adding them comma-separated.
You Should Know
In many cases, you might see concatenation done with the plus sign notation.
SELECT SubscriberKey , EmailAddress , FirstName + ' ' + LastName AS FullName
It might seem cleaner, but there are two downsides to this method:
- With the plus notation, if at least one provided value is
NULL, then the whole outcome becomes
CONCATfunction treats such values as empty strings.
- If you want to join a string with a date type value, you need to use
CONCATwill do the implicit conversion for you.
TRIM function allows you to quickly clean your data by deleting unneeded characters from the beginning and the end of the provided string. By default it deletes whitespace.
SELECT TRIM(' data from webform ') AS CleanData /* Output: 'data from webform' */
There are also two child functions -
LTRIM that works the same but limit the cleaning to just one side of the value.
SELECT LTRIM(' data from webform ') AS CleanLeftSide /* Output: 'data from webform ' */ , RTRIM(' data from webform ') AS CleanRightSide /* Output: ' data from webform' */ , LTRIM(RTRIM(' data from webform ')) AS SameAsTRIM /* Output: 'data from webform' */
You Should Know
You can also use
TRIM to clean specified characters instead of (just) whitespace.
SELECT TRIM('_- ' FROM DirtyWebformData) AS CleanData
In the above code, we firstly added a string
'_- ' containing all characters we want automatically deleted from the beginning and end. Then - after
FROM joiner - the value, we want to clean.
FORMAT function lets you quickly change the data into a different format. In Salesforce Marketing Cloud, it is especially useful, as you cannot create your functions.
FORMAT might save you massive headaches.
FORMAT works only with datetime and numeric values. Why I'm writing about it here then? Because you can use
CONVERT function to change some of your strings into compliant data types. Consider changing string dates into datetime or phone numbers into integers to format them easily. Check out
FORMAT with Dates and
FORMAT with Numbers for more information. The outcome of formatting both number and date types with this function is a string.