SQL SERVER – Puzzle – Get the First Name From Full Name Without CHARINDEX

This First Name From Full Name puzzle excludes CHARINDEX and SUBSTRING. It came from a customer query during a performance health check.

DECLARE @Employee TABLE (ID int IDENTITY(1,1), Name varchar(100));
INSERT @Employee (Name) VALUES ('PINAL DAVE'), ('CHETAN DAVE'), ('BHIM RAO'), ('SRK SINGH');
SELECT ID, Name FROM @Employee ORDER BY ID;
-- Original baseline approaches, prohibited in the submitted answer:
SELECT LEFT(Name, CHARINDEX(' ', Name + ' ') - 1) AS FirstName FROM @Employee;
SELECT SUBSTRING(Name, 0, CHARINDEX(' ', Name, 0)) AS FirstName FROM @Employee;

The four required results are PINAL, CHETAN, BHIM and SRK. The baseline queries show the original requirement. Your submitted solution may use neither CHARINDEX nor SUBSTRING.

Explain a practical alternative and its assumptions. Multiple spaces, single-part names, Unicode and different naming structures need an explicit application requirement. The first space-delimited token isn’t universally a first name.

This example uses a table variable without creating a permanent database or overwriting an EMPLOYEE table. The challenge stays open for readers. The old Monday comment-release promise belonged to the original event.

Related reading

• Comprehensive Database Performance Health Check
• SQL SERVER – Find First Non-Numeric Character from String
• SQL SERVER – Search Text Field – CHARINDEX vs PATINDEX
• SQL SERVER – Finding the Occurrence of Character in String

A first-token expression is not a universal name parser, it is a solution for explicitly stated input rules.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.