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)
29 Comments. Leave new
declare @Name varchar(50)
set @Name=’PINAL DAVE’
select top 1 * from string_split( @Name , ‘ ‘)
SELECT REPLACE(LEFT(REPLACE(Name,’ ‘,’$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$’) ,15),’$’,”) AS FirstName
FROM EMPLOYEE