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)

, , ,
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

  • select PARSENAME(replace(name, ‘ ‘, ‘.’), 2) from EMPLOYEE

    First one was last name, oops

    Reply
    • 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

      Reply
  • 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

    Reply
  • 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

    Reply
  • select LEFT(name,PATINDEX(‘% %’,name))FIRSTNAME FROM EMPLOYEE

    Reply
  • In SQL Server 2016 String_Split function available.
    Select * FROM EMPLOYEE cross apply (select top 1 * from String_Split(Name,’ ‘))as A

    Reply
  • 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)=' '

    Reply
  • select LEFT(NAME,PATINDEX(‘%[^A-Za-z]%’,NAME))
    from EMPLOYEE

    Reply
  • Select ‘Pinal Dave’,stuff(‘Pinal Dave’,PATINDEX(‘%[a-z][a-z][a-z][a-z]%’,’Pinal Dave’),5,”)

    This works for me :)

    Reply
  • 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

    Reply
  • select [name], replace([name],’ ‘,char(0)) as FirstName from EMPLOYEE

    name FirstName
    PINAL DAVE PINAL
    CHETAN DAVE CHETAN
    BHIM RAO BHIM
    SRK SINGH SRK

    Reply
  • Joe Gakenheimer
    February 21, 2018 6:19 pm

    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

    Reply
  • Jothi krishna N
    February 22, 2018 3:36 am

    SELECT (SELECT top 1 value
    FROM STRING_SPLIT( replace(NAME,’ ‘,’,’),’,’))
    from EMPLOYEE

    Reply
  • select name,left(name,PATINDEX( ‘%[^a-z0-9]%’, name )) AS FirstName from employee

    Reply
  • 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

    Reply
  • 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;

    Reply
  • 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.

    Reply
    • 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.

      Reply
  • SELECT left(Name, PATINDEX(‘%[ ]%’,Name)) as Firstname
    FROM EMPLOYEE

    Reply
  • Well, if Regex Assembly can be used;
    select top 1 match from dbo.regexmatches(‘([a-zA-Z])+’,’ firstname lastname’,0)

    Reply
  • ———————
    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 + ‘%’;

    Reply
  • DECLARE @table TABLE (Name VARCHAR(50))
    INSERT INTO @table
    VALUES (‘Pinal Dave’),
    (‘ABC XYZ’)

    SELECT PARSENAME(REPLACE(Name,’ ‘,’.’),2) FROM @table

    Reply

Leave a Reply