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.
LEFT & RIGHT
RIGHT functions allow you to cut down the string to just part of it.
Both require source value and the number of characters to return.
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.
WHERE 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)
I used the optional starting index in the example above, but you will not be using it in most cases. The
CHARINDEX defaults to starting from the beginning so that we can write the above line shorter as
The function returns one of the 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)
WHEN CHARINDEX(' ', InstitutionName) > 0 THEN LEFT(InstitutionName, CHARINDEX(' ', InstitutionName) - 1)
END AS InstitutionType
The above query checks whether there is a space within the
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 a new simplified data point categorising records by type.
|Wildcards & Operators||Description|
|Zero, one or more characters|
|Exactly one character|
|Any single character from the list specified inside|
|Range operator for |
|Exclusion operator for |
To learn more about specific wildcards and advanced operators, check out the
While wildcards give
PATINDEX an edge over
CHARINDEX, it misses one feature of the latter - an optional argument for starting the search from a specific string. Either can be better depending on the use case at hand.
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.
, CONCAT(FirstName, ' ', LastName) AS FullName
You can concatenate any number of strings by just adding them comma-separated.
In many cases, you might see concatenation done with the plus sign notation.
, 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, the 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.
LTRIM and RTRIM
RTRIM functions allow you to quickly clean your data by deleting unneeded spaces from the beginning or the end of the provided string.
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 CleanBothSides /* Output: 'data from webform' */
NEWID function creates a pseudorandom identifier in the form of a 36-character string GUID (RFC4122 compliant).
The output structure is the same as for the SSJS
Platform.Function.GUID() or AMPScript
GUID() functions - five alphanumerical strings joined by hyphens.
SELECT NEWID() AS UniqueID
LOWER, UPPER and Title Case
When working with SQL, you will frequently encounter dirty data that needs some cleaning before being leveraged for personalisation in marketing sends. One of the frequent issues is inconsistent letter cases, especially on self-filled data points like first and last names coming from lead forms.
Thankfully, SQL can clean up such data with built-in
'DIRTY Data' AS SampleData
, LOWER('DIRTY Data') AS Lowercase /* Output: 'dirty data' */
, UPPER('DIRTY Data') AS Uppercase /* Output: 'DIRTY DATA' */
However, there is no out-of-the-box function for Proper Case - a popular requirement, especially around names and titles. The basic solution for single-word strings is straightforward:
FirstName /* Output: 'MATEUSZ' */
, UPPER(LEFT(FirstName, 1)) + LOWER(RIGHT(FirstName, LEN(FirstName) - 1)) AS Propercase /* Output: 'Mateusz' */
However, it will not work with complex (like names with a hyphen in the middle) or multi-word values. The standard SQL solution - User Defined Function - doesn't work in a limited Salesforce Marketing Cloud SQL environment. The alternative - using
PATINDEX to find each word - gets awful as soon as you hit three words and more.
Recommended approach? Either clean the data before ingestion to SFMC or do it directly in the email with the built-in AMPScript
FORMAT with Strings
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.