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

Recently I have been engaged with a customer where they were having a performance issue. They have engaged me via my offering Comprehensive Database Performance Health Check. While doing that, they asked me to write a better way to write a query. While I have given some suggestions and asked them to check, this looks like a nice puzzle to ask. So here we go to see the puzzle – Get the First Name From Full Name Without CHARINDEX.

Here is the sample database, table, and data needed.

-- Database Creation
CREATE DATABASE SQLAuthority;
GO
USE SQLAuthority;
GO
-- Table Creation
CREATE TABLE EMPLOYEE (ID INT IDENTITY(1,1), NAME VARCHAR(100));
GO
-- Sample Data
INSERT INTO EMPLOYEE (NAME) VALUES('PINAL DAVE');
GO
INSERT INTO EMPLOYEE (NAME) VALUES('CHETAN DAVE');
GO
INSERT INTO EMPLOYEE (NAME) VALUES('BHIM RAO');
GO
INSERT INTO EMPLOYEE (NAME) VALUES('SRK SINGH');
-- Have a look at data
SELECT * FROM EMPLOYEE

The Requirement

We are storing first name and last name together in one column called “NAME”. we need to get the only first name using a query.

Possible Solutions

Here are two workable solutions.

SELECT LEFT(Name, CHARINDEX(' ', Name + ' ') - 1) AS FirstName
FROM EMPLOYEE

SELECT SUBSTRING(Name, 0, CHARINDEX(' ', Name, 0)) AS FirstName
FROM EMPLOYEE

THE PUZZLE

What are the other practical solutions without using CHARINDEX AND SUBSTRING?

Here are few additional blog posts which can help you to answer this puzzle:

• 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

Please post your answer in the comment section. I will publish all the valid answers on next Monday. Please share this with your friends and see if they can solve this puzzle.

Reference: Pinal Dave (https://blog.sqlauthority.com)