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 EMPLOYEESELECT SUBSTRING(Name, 0, CHARINDEX(' ', Name, 0)) AS FirstName
FROM EMPLOYEETHE 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
select PARSENAME(replace(name, ‘ ‘, ‘.’), 2) from EMPLOYEE
First one was last name, oops
Beat me to it by a long margin. :)
SELECT
PARSENAME(fn.DottedName, 2) as LastName,
PARSENAME(fn.DottedName, 1) as FirstName
FROM EMPLOYEE
CROSS APPLY (
SELECT REPLACE(NAME, ‘ ‘, ‘.’) as DottedName
) fn
CREATE TABLE #FirstName
(
FirstName NVarChar(50)
)
DECLARE @FullName NVarChar(200)
DECLARE FirstName CURSOR
FOR SELECT [NAME] FROM EMPLOYEE
OPEN FirstName
FETCH NEXT FROM FirstName into @FullName
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #FirstName
SELECT TOP 1 Value from string_split(@FullName,’ ‘)
FETCH NEXT FROM FirstName into @FullName
END
CLOSE FirstName
DEALLOCATE FirstName
select * from #FirstName
In SQL Server 2016, this query works for me:
SELECT T.ID, T.NAME, T.value FirstName
FROM
(SELECT *, ROW_NUMBER ( ) OVER(PARTITION BY NAME ORDER BY NAME) RowNumber
FROM EMPLOYEE CROSS APPLY STRING_SPLIT(NAME, ‘ ‘)) T
WHERE T.RowNumber = 1
Yeah. STRING_SPLIT fits perfectly here.
select LEFT(name,PATINDEX(‘% %’,name))FIRSTNAME FROM EMPLOYEE
In SQL Server 2016 String_Split function available.
Select * FROM EMPLOYEE cross apply (select top 1 * from String_Split(Name,’ ‘))as A
with Numb AS
(
select 1 as i
union all
select i + 1
from Numb
where i<50
)
select *
from Employee AS E
cross apply
(select left(e.name,i) as x
from Numb as n) as c(x)
where left(reverse(c.x),1)=' '
select LEFT(NAME,PATINDEX(‘%[^A-Za-z]%’,NAME))
from EMPLOYEE
Select ‘Pinal Dave’,stuff(‘Pinal Dave’,PATINDEX(‘%[a-z][a-z][a-z][a-z]%’,’Pinal Dave’),5,”)
This works for me :)
Hi Dave, I tried something let me know if this works.
;with cte as
(
SELECT value,ROW_NUMBER() over (partition by id order by id) as row_id
FROM EMPLOYEE as a
CROSS APPLY STRING_SPLIT(NAME, ‘ ‘)
)
select value as firstname from cte
where row_id = 1
select [name], replace([name],’ ‘,char(0)) as FirstName from EMPLOYEE
name FirstName
PINAL DAVE PINAL
CHETAN DAVE CHETAN
BHIM RAO BHIM
SRK SINGH SRK
Perfect!
did you see the result in text
I always forget about PATINDEX. It is very helpful. Below is the simplest I could come up with.
SELECT LEFT(NAME, PATINDEX(‘% %’, NAME)) AS FirstName, LTRIM(RIGHT(NAME, PATINDEX(‘% %’, NAME) – 1)) AS LastName, NAME AS FullName
FROM Employee
SELECT (SELECT top 1 value
FROM STRING_SPLIT( replace(NAME,’ ‘,’,’),’,’))
from EMPLOYEE
select name,left(name,PATINDEX( ‘%[^a-z0-9]%’, name )) AS FirstName from employee
Declare @sql varchar(100)=’Mohammad waheed’, @len int, @i int =0,@FirstNameCounter int, @LastNameCounter int
set @len=len(@sql)
set @i=@len
While @i>0
Begin
if right(@sql,@i) like ‘ %’
Begin
set @LastNameCounter=@i
break
End
set @i=@i-1
End
Set @FirstNameCounter=@len-@i
select LEFT(@sql,@FirstNameCounter) FristName, right(@sql,@LastNameCounter) LastName
DECLARE @table TABLE (Name VARCHAR(50))
INSERT INTO @table
VALUES (‘Pinal Dave’),
(‘Krishnraj Rana’)
;WITH cteCombinedName AS
(
SELECT CAST(” + REPLACE(Name,’ ‘,”) + ” AS XML) AS CombinedName
FROM @table
)
SELECT
CombinedName.value(‘/x[1]’,’VARCHAR(50)’) AS FirstName, –first part
CombinedName.value(‘/x[2]’,’VARCHAR(50)’) AS LastName –second part
FROM cteCombinedName;
I have a space in my first name, How does it work? I know its an edge case. I think without a proper delimiter you cant address all the possible scenarios.
Oh yeah, there could be that case as well. Never thought about having a space in first name itself. If that can my and other solution would fail. But how would be differentiate whether space is within the first name? Then you should not store First and Last in same column.
SELECT left(Name, PATINDEX(‘%[ ]%’,Name)) as Firstname
FROM EMPLOYEE
Well, if Regex Assembly can be used;
select top 1 match from dbo.regexmatches(‘([a-zA-Z])+’,’ firstname lastname’,0)
select dbo.regexreplace(ltrim(Name),’\s+.*’,”) from Employee
in SQL Server???
———————
DECLARE @RunningIndex INT = 1,
@FirstName VARCHAR(20),
@CurrentChar CHAR(1),
@FullName VARCHAR(10) = ‘PINAL DAVE’;
WHILE LEN(@FullName) > @RunningIndex AND ISNULL(@CurrentChar, ‘1’) ‘ ‘
BEGIN
PRINT @RunningIndex;
SET @FirstName = RTRIM(ISNULL(@FirstName, ”) + ISNULL(@CurrentChar, ”));
PRINT @FirstName;
SELECT @CurrentChar = RIGHT(LEFT(@FullName, @RunningIndex), 1);
SET @RunningIndex += 1;
END;
SELECT @FirstName;
———————————-
GO
———————————-
DECLARE @FullName VARCHAR(10) = ‘PINAL DAVE’;
SELECT TOP 1
value
FROM STRING_SPLIT(@FullName, ‘ ‘)
WHERE @FullName LIKE value + ‘%’;
DECLARE @table TABLE (Name VARCHAR(50))
INSERT INTO @table
VALUES (‘Pinal Dave’),
(‘ABC XYZ’)
SELECT PARSENAME(REPLACE(Name,’ ‘,’.’),2) FROM @table