The customer export gives you one name field, while the report asks for three. Splitting full names produces useful candidates, but those candidates still need an agreed naming rule.
Separate Parsing From Knowing a Name
A space tells you where a token ends. It doesn't tell you which tokens belong to a family name. That distinction matters before any script writes first, middle, and last columns.
The examples use synthetic customer names and a positional rule. The first token becomes the first candidate. The last token becomes the last candidate, with intervening text retained as a middle candidate.
I ask how the fields will be used before choosing a parser. Mailing labels need different certainty from a casual search display. A payment or identity process needs confirmed fields rather than guesses.
Preserve the original FullName value throughout the work. Store candidates separately, along with their review status. Don't replace a person's entered name because a string function found a space.
Normalize the Whitespace You Support
Extra leading, trailing, and repeated spaces create empty pieces. Tabs and nonbreaking spaces also appear in imported data. This example explicitly converts those two separators to ordinary spaces.
It leaves punctuation, accents, and letter case intact. Other Unicode whitespace requires an additional approved normalization rule. Keep that decision separate from the positional split itself.
CREATE TABLE #Names
(
CustomerId int NOT NULL PRIMARY KEY,
FullName nvarchar(200) NULL,
NormalizedName nvarchar(200) NULL
);
INSERT #Names (CustomerId, FullName)
VALUES (1, N' Alex Morgan '),
(2, N'Jordan Avery Smith'),
(3, N'Ana de Cruz'),
(4, N'Pat Smith Jr.'),
(5, N'Lee'),
(6, NULL);
UPDATE #Names
SET NormalizedName = LTRIM(RTRIM(REPLACE(REPLACE(
FullName, NCHAR(9), N' '), NCHAR(160), N' ')));
WHILE EXISTS (SELECT 1 FROM #Names
WHERE NormalizedName LIKE N'% %')
BEGIN
UPDATE #Names
SET NormalizedName = REPLACE(NormalizedName, N' ', N' ')
WHERE NormalizedName LIKE N'% %';
END;
SELECT CustomerId, FullName, NormalizedName
FROM #Names ORDER BY CustomerId;Run the blocks in order within the same connection. The temporary table keeps the source and normalized values together. NULL stays NULL, while an all-space value becomes an empty string.
The repeated replacement finishes when no doubled spaces remain. For a large import, normalize once during staging rather than inside every report. Measure that work on your own data before choosing an implementation.
Keep normalization reversible by retaining the untouched source column. An import error investigation needs to see what arrived. Comparing only the cleaned value hides the separator that caused the problem.
Set column lengths from the source contract rather than these sample values. Reject truncation before parsing begins. A missing ending token cannot be recovered from a string already cut short.
Splitting Full Names at the First and Last Space
CHARINDEX locates the first separator. REVERSE lets the same function locate the final separator from the other end. Subtract that position from the string length to recover its original location.
A single-token name needs a separate path. Otherwise, a missing space produces an invalid substring length. Empty and NULL values need separate status flags too.
;WITH Positions AS
(
SELECT CustomerId, FullName, NormalizedName,
CHARINDEX(N' ', NormalizedName) AS FirstSpace,
CASE WHEN CHARINDEX(N' ', NormalizedName) > 0
THEN LEN(NormalizedName)
- CHARINDEX(N' ', REVERSE(NormalizedName)) + 1
ELSE 0 END AS LastSpace
FROM #Names
)
SELECT CustomerId, FullName,
CASE WHEN FirstSpace = 0 THEN NULLIF(NormalizedName, N'')
ELSE LEFT(NormalizedName, FirstSpace - 1) END AS FirstCandidate,
CASE WHEN LastSpace > FirstSpace
THEN SUBSTRING(NormalizedName, FirstSpace + 1,
LastSpace - FirstSpace - 1) END AS MiddleCandidate,
CASE WHEN LastSpace > 0
THEN RIGHT(NormalizedName, LEN(NormalizedName) - LastSpace) END
AS LastCandidate,
CASE WHEN NULLIF(NormalizedName, N'') IS NULL THEN N'Missing name'
WHEN FirstSpace = 0 THEN N'Single token: review'
ELSE N'Positional candidate: unconfirmed' END AS ReviewStatus
FROM Positions
ORDER BY CustomerId;Two-word input leaves the middle candidate NULL. Three-word input places the center token there. Longer input retains every token between the first and last separator.
That behavior is predictable, but it doesn't establish a naming convention. Ana de Cruz exposes the problem immediately. The candidate de doesn't prove that de is a middle name.
The first-space and last-space calculations use the normalized string throughout. Mixing raw lengths with cleaned positions shifts boundaries. Keep each intermediate value tied to the same text representation.
Avoid reusing a dotted-identifier parser for personal names. Dots and spaces have different meanings in this data. Names also exceed any fixed number of identifier components.
Splitting Full Names With STRING_SPLIT Ordinals
SQL Server 2022 adds the ordinal option to STRING_SPLIT. The third argument must be a constant bit or integer value. Pass 1 to receive each token's original position.
STRING_SPLIT requires database compatibility level 130 or higher. The ordinal option also needs an engine that supports it. Check the database setting before using this block.
SELECT compatibility_level
FROM sys.databases WHERE database_id = DB_ID();
SELECT n.CustomerId, s.ordinal, s.value AS NameToken
FROM #Names AS n
CROSS APPLY STRING_SPLIT(n.NormalizedName, N' ', 1) AS s
WHERE s.value <> N''
ORDER BY n.CustomerId, s.ordinal;The ordinal column describes position, not guaranteed delivery order. Keep ORDER BY when displaying the tokens. Rebuilding names without it turns a predictable parsing task into an avoidable ordering problem.
This method helps inspect how many pieces a value contains. It also supports rules that examine several tokens. Those rules still operate on text, not confirmed family relationships.
Flag Suffixes and Compound Surnames
A suffix such as Jr. looks like the last word to a positional parser. More than three tokens also deserve attention. The sample flags several surname particles as an additional review signal.
;WITH Tokens AS
(
SELECT n.CustomerId, s.value, s.ordinal
FROM #Names AS n
CROSS APPLY STRING_SPLIT(n.NormalizedName, N' ', 1) AS s
WHERE s.value <> N''
), Signals AS
(
SELECT CustomerId, COUNT_BIG(*) AS TokenCount,
MAX(CASE WHEN UPPER(REPLACE(value, N'.', N''))
IN (N'JR', N'SR', N'II', N'III', N'IV')
THEN 1 ELSE 0 END) AS HasSuffix,
MAX(CASE WHEN UPPER(value) IN (N'DE', N'DEL', N'VAN', N'VON')
THEN 1 ELSE 0 END) AS HasParticle
FROM Tokens GROUP BY CustomerId
)
SELECT n.CustomerId, n.FullName,
CASE WHEN s.CustomerId IS NULL THEN N'Missing name'
WHEN s.TokenCount = 1 THEN N'Single token: review'
WHEN s.HasSuffix = 1 THEN N'Suffix: review'
WHEN s.HasParticle = 1 THEN N'Possible compound surname: review'
WHEN s.TokenCount > 3 THEN N'Long name: review'
ELSE N'Positional candidate: unconfirmed' END AS ReviewStatus
FROM #Names AS n
LEFT JOIN Signals AS s ON s.CustomerId = n.CustomerId
ORDER BY n.CustomerId;These signals don't form a complete list of naming traditions. Compound surnames also exist without a listed particle. Even a plain three-word value can divide differently from the positional assumption.
I keep review rules visible beside the generated candidates. I don't call the unflagged rows verified. A missing warning tells you the rule found nothing, not that the person's name was understood.
Assign each warning a reason your reviewer can understand. Preserve several reasons when several rules apply. A single priority message is convenient for display, but loses detail for auditing.
Use the source system's identifier to match a correction back to its row. Names aren't reliable unique keys. Two identical display names still represent separate records requiring separate confirmation.
Test Splitting Full Names on Edge Cases
Test NULL, empty text, one token, and repeated separators. Include suffixes, punctuation, and names supplied in family-name-first order. Compare the candidates with confirmed source fields wherever those fields exist.
For splitting full names, distinguish a processing error from an uncertain interpretation. The parser should handle supported text consistently. The review process should handle uncertain meaning without silently changing the original.
Which field can your customer correct without calling support? That answer helps decide whether candidates should appear at all. A parser has no passport office inside it.
Review the parser under the actual database collation. Letter comparisons and sorting follow that collation's rules. Decide how suffix comparisons should work before importing text from several sources.
Collect reviewed examples as regression inputs for the next rule change. Include cases the previous rule mishandled. Passing those examples demonstrates consistent behavior without proving universal correctness.
Store Confirmation as Its Own Fact
Keep the original name, candidate fields, rule version, and confirmation status together. A later rule change should produce new candidates without erasing prior confirmation. Avoid treating every imported value as an automatic approval.
Use splitting full names to assist a workflow that accepts uncertainty. Prefer separately collected name fields when the business needs confirmed components. Let the person's confirmed input outrank a positional shortcut.
Related reading on this blog: LTRIM and RTRIM With Custom Characters in SQL Server 2022 and Fix Error: Invalid object name STRING_SPLIT.
A parsed name is not a confirmed identity, it is a candidate that needs the right context.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
