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 use a 1-based index instead of 0-based indexing known from, for example, JavaScript. The first character starts at index 1. It is crucial to provide the correct value to the arguments and correctly interpret the results.

LEFT & RIGHT

LEFT and 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 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 SubscriberKey
FROM _Subscribers
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 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)

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 CHARINDEX("Manager", JobTitle).

The function returns one of the 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 the 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 a new simplified data point categorising records by type.

PATINDEX

While CHARINDEX is great for searching the position of a specific string in a value, PATINDEX gives you even more power by supporting the wildcard patterns used with LIKE operator:

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 LIKE guide.

Broad C-Suite hunt with PATINDEX

PATINDEX('%C_O%', JobTitle)

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

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, the 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.

LTRIM, RTRIM and TRIM

LTRIM and RTRIM functions allow you to quickly clean your data by deleting unneeded spaces from the beginning or the end of the provided string.

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' */
    , TRIM('   data from webform ')         AS CleanBothSides   /* Output: 'data from webform' */

NEWID

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.

Returns GUID in the form of XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX

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 LOWER and UPPER.

SELECT
      '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:

SELECT
      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 CHARINDEX/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 ProperCase function.

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.