SFMC SQL String Functions

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

All string functions in SQL are using a 1-based index instead of 0-based indexing known from, for example, JavaScript. The first character starts at index 1. It is crucial for both providing the correct value to the parameters, as well as for correctly interpreting the results.

LEFT & RIGHT#

LEFT and 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.

LEFT and RIGHT can work both on strings as well as on other data types
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#

LEN function returns a count of characters in the value.

If you use Salesforce Contact ID as Subscriber Key, use LEN to find invalid records quickly
SELECT
s.SubscriberKey
FROM _Subscribers s
WHERE LEN(s.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 MAX function.

CHARINDEX#

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.

Search for 'Manager' in the JobTitle column starting with the first index
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 CHARINDEX("Manager", JobTitle).

The function returns one three possible values:

  1. NULL - if either value to find or searched value is NULL
  2. 0 - if the value to find is not within the searched value
  3. 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.

By default, CHARINDEX in SFMC is case-insensitive in its searches. You can, however, enforce case-sensitivity by adding COLLATE:

Case sensitive CHARINDEX search - note lack of a comma between the value and COLLATE
CHARINDEX("Manager", JobTitle COLLATE Latin1_General_CS_AS)
You Should Know

CHARINDEX is especially useful when combined with CASE and LEFT/RIGHT functions, as it allows for smart string operations:

Dynamically pull part of the Institution Name value
SELECT
CASE
WHEN CHARINDEX(' ', InstitutionName) > 0 THEN LEFT(InstitutionName, CHARINDEX(' ', InstitutionName) - 1)
ELSE InstitutionName
END AS InstitutionType
FROM 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 University of..., School of... - by taking only the first part, we can create new simplified data point categorizing records by type.

CONCAT#

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.

Perfect for preparing your data for personalization
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.

Same as above, but with the plus notation
SELECT
SubscriberKey,
EmailAddress,
FirstName + ' ' + LastName AS FullName

It might seem cleaner, but there are two downsides to this method:

  1. With the plus notation, if at least one provided value is NULL, then the whole outcome becomes NULL. CONCAT function treats such values as empty strings.
  2. If you want to join a string with a date type value, you need to use CONVERT, but CONCAT will do the implicit conversion for you.

TRIM#

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.

Noone likes useless whitespace in their database
SELECT
TRIM(' data from webform ') AS CleanData /* Output: 'data from webform' */

There are also two child functions - RTRIM and LTRIM that works the same but limit the cleaning to just one side of the value.

Noone likes useless whitespace in their database
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.

No more '__-- Owner --__' in your values
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 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.

The 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.

Last updated on by Mateusz Dąbrowski