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

SQL SERVER - Puzzle - Get the First Name From Full Name Without CHARINDEX puzzle 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:

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)

SQL Function, SQL Scripts, SQL Server, SQL String
Previous Post
SQL SERVER – Puzzle – Strange Behavior With Extra Comma in Create Table Statement
Next Post
SQL SERVER – Puzzle – How Does Table Qualifier Work in INSERT Statement?

Related Posts

29 Comments. Leave new

  • declare @Name varchar(50)
    set @Name=’PINAL DAVE’
    select top 1 * from string_split( @Name , ‘ ‘)

    Reply
  • Sumit Salunkhe
    April 18, 2019 5:27 pm

    SELECT REPLACE(LEFT(REPLACE(Name,’ ‘,’$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$’) ,15),’$’,”) AS FirstName
    FROM EMPLOYEE

    Reply

Leave a Reply