String functions — Marketing Cloud SQL reference

String functions in MC SQL are a subset of T-SQL's. The list is short, the syntax is standard, and the production bugs are mostly about three things: NULL propagation through + concatenation, length truncation when you LEFT() to fit a destination column, and the index-killing effect of wrapping a column in LOWER() inside a WHERE.

Official syntax

-- Length (in characters, not bytes — Unicode-safe)
SELECT LEN(EmailAddress) AS EmailLen FROM master_subscribers;

-- Substring extraction
SELECT
  LEFT(EmailAddress, 5)                AS FirstFive,
  RIGHT(EmailAddress, 4)               AS LastFour,
  SUBSTRING(EmailAddress, 2, 6)        AS PositionTwoLenSix
FROM master_subscribers;

-- Trim whitespace
SELECT LTRIM(RTRIM(SubscriberKey)) AS KeyClean FROM _Subscribers;

-- Case conversion
SELECT
  LOWER(EmailAddress) AS EmailLower,
  UPPER(LoyaltyTier)  AS TierUpper
FROM master_subscribers;

-- Replace
SELECT REPLACE(PhoneNumber, '-', '') AS PhoneClean
FROM master_subscribers;

-- Find position (returns 0 if not found, NOT -1)
SELECT
  EmailAddress,
  CHARINDEX('@', EmailAddress) AS AtPosition
FROM master_subscribers;

-- Concatenation: use CONCAT (NULL-safe) over + (NULL-poisoning)
SELECT
  CONCAT(FirstName, ' ', LastName)        AS FullNameSafe,
  FirstName + ' ' + LastName              AS FullNameRisky
FROM master_subscribers;

The supported set across most SFMC tenants:

| Function | What it does | Returns | |---|---|---| | LEN(s) | Character length (trailing spaces ignored) | INT | | LEFT(s, n) | First n characters | NVARCHAR | | RIGHT(s, n) | Last n characters | NVARCHAR | | SUBSTRING(s, start, len) | len chars starting at position start (1-indexed) | NVARCHAR | | LTRIM(s) / RTRIM(s) | Trim leading / trailing whitespace | NVARCHAR | | LOWER(s) / UPPER(s) | Case fold | NVARCHAR | | REPLACE(s, old, new) | Replace all occurrences of old with new | NVARCHAR | | CHARINDEX(needle, haystack) | 1-indexed position of needle in haystack, or 0 if not found | INT | | CONCAT(s1, s2, ...) | Concatenate, NULL-safe (NULLs become empty strings) | NVARCHAR | | s1 + s2 | Concatenate, NULL-poisoning (NULL anywhere → NULL result) | NVARCHAR |

Reference:

• Salesforce Help — Supported T-SQL syntax (functions section) ↗

What survives in production

Use CONCAT, not +, when any operand can be NULL

'Hello ' + NULL + 'World' returns NULL, not 'Hello World'. Every + concatenation involving a column that could be NULL is a silent-NULL trap.

-- AT RISK — if FirstName or LastName is NULL, the result is NULL
SELECT FirstName + ' ' + LastName AS FullName
FROM master_subscribers;

-- DURABLE — CONCAT treats NULL as empty string
SELECT CONCAT(FirstName, ' ', LastName) AS FullName
FROM master_subscribers;

-- Or, if you must use + (e.g. for an alias that needs the explicit
-- middle space), wrap each operand in COALESCE
SELECT COALESCE(FirstName, '') + ' ' + COALESCE(LastName, '') AS FullName
FROM master_subscribers;

+ propagates NULL for backward T-SQL compatibility. CONCAT doesn't. Pick CONCAT by default unless you want NULL behavior intentionally.

LTRIM(RTRIM(...)) is the SubscriberKey safety net

When joining SubscriberKey across DEs (or against _Subscribers), trailing whitespace from a CSV import or a CRM export is the silent killer. Always normalize on both sides, even when you're sure the data is clean — the cost is one extra function call per row, the saving is the post-mortem you don't have to write.

-- DEFENSIVE — even when SubscriberKey looks clean, normalize
SELECT s.SubscriberKey, e.LoyaltyTier
FROM _Subscribers s
INNER JOIN ext_loyalty e
  ON LTRIM(RTRIM(CAST(s.SubscriberKey AS NVARCHAR(255))))
   = LTRIM(RTRIM(CAST(e.UserId AS NVARCHAR(255))));

See JOIN and gotchas — #9 for the full SubscriberKey type-coercion + trim trap.

LOWER() / UPPER() in WHERE kills index use — stage instead

Wrapping a column in LOWER() for a case-insensitive comparison forces a full table scan because the index can't be used. On a 5M-row Data Extension that's the difference between a 30-second query and a 30-minute timeout.

-- AT RISK — LOWER() on the column kills the index, full scan
SELECT SubscriberKey
FROM master_subscribers
WHERE LOWER(LoyaltyTier) = 'gold';

-- BETTER WHEN POSSIBLE — normalize at write time, not at read time.
-- Stage a LoyaltyTierLower column in a de_stg_*, then index that.
INSERT INTO de_stg_subscribers_normalized
SELECT
  SubscriberKey,
  LoyaltyTier,
  LOWER(LoyaltyTier) AS LoyaltyTierLower
FROM master_subscribers;

-- Then production reads against the normalized column:
SELECT SubscriberKey
FROM de_stg_subscribers_normalized
WHERE LoyaltyTierLower = 'gold';

The trade is more storage + a staging step, in exchange for fast reads forever after. Pay it once.

LEFT() to fit a destination column = silent truncation

If your destination DE has EmailAddress VARCHAR(50) and your source is up to 75 chars, LEFT(EmailAddress, 50) silently truncates without surfacing which rows lost data. Either resize the destination, or count the truncations explicitly.

-- DIAGNOSTIC — count rows that would be truncated, before doing it
SELECT
  COUNT(*) AS TotalRows,
  SUM(CASE WHEN LEN(EmailAddress) > 50 THEN 1 ELSE 0 END) AS WouldTruncate
FROM master_subscribers;

Run this against production before changing column lengths or adding LEFT() calls. See gotchas — #5.

Quick decision

Use CONCAT instead of + when:

• Any operand could be NULL.
• Default for new code. + is only useful when you specifically want NULL propagation.

Use LTRIM(RTRIM(...)) always when:

• Joining or comparing SubscriberKey or any external identifier.

Stage LOWER() / UPPER() results into a column when:

• The source DE is large and the comparison runs frequently. Read-time normalization is fine for one-shot queries; for production, normalize at write time.

Use LEFT() / SUBSTRING() carefully when:

• The destination column length is fixed. Run the diagnostic count first.

Reach for CHARINDEX instead of LIKE when:

• You only need to know whether a substring is present (and you don't need wildcards). It's faster than LIKE '%x%' and the intent is clearer.

Related

• Basics — supported T-SQL subset
• SELECT — string functions in projection
• WHERE — string functions in filters (and the index-killing trap)
• JOIN — LTRIM(RTRIM()) for SubscriberKey safety
• LIKE — pattern matching (related but different from CHARINDEX)
• MC SQL gotchas — see #5 (length truncation), #9 (SubscriberKey trim/cast)

More function reference pages incoming: Date · Numeric · Conversion · Aggregate · Null Functions.

Plus how-to snippets for common production debugging — email sends, value length, contact reach, etc.