Splitting the full name in Prefix, First Name, Middle Name and Last Name in SQL Server

Question

I am trying to split a full name with Prefix, First Name, Middle Name and Last Name using SQL Server.

I have tried the following but it is not working properly as desired output is not correct.

Code

SELECT TOP 10 
    ID,
    FullName,
    SUBSTRING(FullName, CHARINDEX(' ', FullName) + 1, LEN(FullName)) AS Full_Name,
    SUBSTRING(FullName, 1, CHARINDEX(' ', FullName)) AS Prefix,
    SUBSTRING(FullName, CHARINDEX(' ', FullName), LEN(FullName) - CHARINDEX(' ', REVERSE(FullName)) - CHARINDEX(' ', FullName) + 1) FirstName,
    RIGHT(FullName, CHARINDEX(' ', REVERSE(FullName)) - 1) AS Last_Name,
    LEN(SUBSTRING(FullName, CHARINDEX(' ', FullName), LEN(FullName) - CHARINDEX(' ', REVERSE([FullName)) - CHARINDEX(' ', FullName) + 1)) AS FirstNameChar
FROM
    Names WITH (NOLOCK)
WHERE
    (FullName LIKE 'Mr %' OR 
     FullName LIKE 'Mrs %' OR  
     FullName LIKE 'Miss %' OR  
     FullName LIKE 'Ms %' OR
     FullName LIKE 'Dr %')

Current output:

ID	FullName	Prefix	FirstName	Last_Name	FirstNameChar
123456	Mr s t Fenech	Mr	s t	Fenech	4

Required output:

ID	FullName	Prefix	FirstName	Middle_Name	Last_Name	FirstNameChar
123456	Mr s t Fenech	Mr	s	t	Fenech	1

I want to remove the spaces from before the first name and Last Name as can be see the FirstNameChar is 4 when actually should be 1 and be able to split also the middle name.

Anyone can help with this?

Reference to question on stackoverflow: SQL- Get the substring after first space and second space in separate columns

SQL: parse the first, middle and last name from a fullname field

Parse Prefix First Middle Last Suffix from full name

The simple answer is, you don't. [Falsehoods Programmers Believe About Names](https://www.kalzumeus.com/2010/06/17/falsehoods-programmers-believe-about-names/) This question has been asked many times, and the truth is that unless you ask the person how their name is split up, you will always get it wrong. — Thom A, Jul 20 '21 at 14:59
Stop splattering your code with [nolock](https://www.sentryone.com/blog/aaronbertrand/bad-habits-nolock-everywhere) — SMor, Jul 20 '21 at 18:54

mathewspete · Answer 1 · 2021-07-21T16:47:06.137

0

IF you do know that the names are always formatted the same (e.g., [prefix][space][first][space][middle][space][last] ), you can do something like this:

SELECT SUBSTRING('Mr s t Fenech', CHARINDEX(' ', 'Mr s t Fenech') + 1, LEN('Mr s t Fenech')) AS Full_Name
, REVERSE(PARSENAME(REPLACE(REVERSE('Mr s t Fenech'), ' ', '.'), 1)) AS Prefix
, REVERSE(PARSENAME(REPLACE(REVERSE('Mr s t Fenech'), ' ', '.'), 2)) AS FirstName
, REVERSE(PARSENAME(REPLACE(REVERSE('Mr s t Fenech'), ' ', '.'), 3)) AS MiddleName
, REVERSE(PARSENAME(REPLACE(REVERSE('Mr s t Fenech'), ' ', '.'), 4)) AS LastName
, LEN(REVERSE(PARSENAME(REPLACE(REVERSE('Mr s t Fenech'), ' ', '.'), 1))) AS PrefixLen
, LEN(REVERSE(PARSENAME(REPLACE(REVERSE('Mr s t Fenech'), ' ', '.'), 2))) AS FirstNameLen
, LEN(REVERSE(PARSENAME(REPLACE(REVERSE('Mr s t Fenech'), ' ', '.'), 3))) AS MiddleNameLen
, LEN(REVERSE(PARSENAME(REPLACE(REVERSE('Mr s t Fenech'), ' ', '.'), 4))) AS LastNameLen

But replace 'Mr s t Fenech' with FullName.

edited Jul 21 '21 at 16:47

answered Jul 20 '21 at 17:19

mathewspete

38
1
6

Try this with Lord Andrew Lloyd Webber and you'll incorrectly define his middle name as "Lloyd" and his surname as "Webber". – Thom A Jul 20 '21 at 19:03
*"Like I said:"*? – Thom A Jul 21 '21 at 16:54
@Larnu Like I said: > IF you do know that the names are always formatted the same. . . `Ms Jamie Lynn Marie Spears` is another that will not work correctly. Saying, "The simple answer is, you don't," because there are outliers is like saying don't write any code because something can break your code. I edited my answer for clarification. – mathewspete Jul 21 '21 at 17:20

Rahul Biswas · Answer 2 · 2021-07-21T20:36:40.530

This query is applicable for all combinations. As prefix with a space is mandatory so can check with (first_name) or (first_name & last_name) or (first_name & middle_name & last_name) with prefix.

    SELECT TOP 10
     ID
     , FullName
     , SUBSTRING(FullName, 1, CHARINDEX(' ', FullName) - 1) Prefix
     , CASE WHEN CHARINDEX(' ', FullName) = 0 
               THEN NULL
            ELSE SUBSTRING(FullName, CHARINDEX(' ', FullName) + 1, (CASE WHEN CHARINDEX(' ', SUBSTRING(FullName, CHARINDEX(' ', FullName) + 1, LEN(FullName))) = 0 
                                                                      THEN LEN(FullName)
                                                                   ELSE CHARINDEX(' ', SUBSTRING(FullName, CHARINDEX(' ', FullName) + 1, LEN(FullName))) - 1
                                                              END)) 
       END FirstName
     , CASE WHEN CHARINDEX(' ', SUBSTRING(FullName, CHARINDEX(' ', SUBSTRING(FullName, CHARINDEX(' ', FullName) + 1, LEN(FullName))) + 1 + CHARINDEX(' ', FullName), LEN(FullName))) = 0
               THEN NULL
            ELSE SUBSTRING(FullName, CHARINDEX(' ', SUBSTRING(FullName, CHARINDEX(' ', FullName) + 1, LEN(FullName))) + 1 + CHARINDEX(' ', FullName), 
               CASE WHEN CHARINDEX(' ', SUBSTRING(FullName, CHARINDEX(' ', FullName) + 1, LEN(FullName))) = 0
                       THEN LEN(FullName)
                    ELSE ((CHARINDEX(' ', SUBSTRING(FullName, CHARINDEX(' ', SUBSTRING(FullName, CHARINDEX(' ', FullName) + 1, LEN(FullName))) + 1 + CHARINDEX(' ', FullName), LEN(FullName)))) - 1) END)
       END MiddleName
     , CASE WHEN (CHARINDEX(' ', SUBSTRING(FullName, CHARINDEX(' ', SUBSTRING(FullName, CHARINDEX(' ', FullName) + 1, LEN(FullName))) + 1 + CHARINDEX(' ', FullName), LEN(FullName))) > 0
               OR CHARINDEX(' ', SUBSTRING(FullName, CHARINDEX(' ', FullName) + 1, LEN(FullName))) > 0)
               THEN CASE WHEN CHARINDEX(' ', SUBSTRING(FullName, CHARINDEX(' ', SUBSTRING(FullName, CHARINDEX(' ', SUBSTRING(FullName, CHARINDEX(' ', FullName) + 1, LEN(FullName))) + 1 + CHARINDEX(' ', FullName), LEN(FullName))) + 1 + CHARINDEX(' ', FullName) + CHARINDEX(' ', SUBSTRING(FullName, CHARINDEX(' ', FullName) + 1, LEN(FullName))), LEN(FullName))) = 0
                            THEN RIGHT(FullName, CHARINDEX(' ', REVERSE(FullName)) - 1)
                         ELSE SUBSTRING(FullName, CHARINDEX(' ', SUBSTRING(FullName, CHARINDEX(' ', SUBSTRING(FullName, CHARINDEX(' ', FullName) + 1, LEN(FullName))) + 1 + CHARINDEX(' ', FullName), LEN(FullName))) + 1 + CHARINDEX(' ', FullName) + CHARINDEX(' ', SUBSTRING(FullName, CHARINDEX(' ', FullName) + 1, LEN(FullName))), LEN(FullName))
                    END
            ELSE NULL
       END LastName
     , LEN(CASE WHEN CHARINDEX(' ', FullName) = 0 
                     THEN NULL
                  ELSE SUBSTRING(FullName, CHARINDEX(' ', FullName) + 1, (CASE WHEN CHARINDEX(' ', SUBSTRING(FullName, CHARINDEX(' ', FullName) + 1, LEN(FullName))) = 0 
                                                                          THEN LEN(FullName)
                                                                       ELSE CHARINDEX(' ', SUBSTRING(FullName, CHARINDEX(' ', FullName) + 1, LEN(FullName))) - 1
                                                                  END)) 
             END) FirstNameChar
FROM dbo.fullNameTest
WHERE
    (FullName LIKE 'Mr %' OR 
     FullName LIKE 'Mrs %' OR  
     FullName LIKE 'Miss %' OR  
     FullName LIKE 'Ms %' OR
     FullName LIKE 'Dr %');

Also check this url https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=b4a02827d400ac4cadad852bbae96c57

Hi @alphasqrd: Please check my answer. I think it'll meet your expectations. Though it's very difficult to splitting names because of their formation. I've handled here few name like Mr Lionel Messi, Mrs Van Dar Sar, Miss Lara, Dr Devi Shetti, & Mr Patrick D-DAR WAK CICILEN. Please check from above url where you'll get query with table definition and feel free to ask if any query. Thanks — Rahul Biswas, Jul 21 '21 at 20:46

Splitting the full name in Prefix, First Name, Middle Name and Last Name in SQL Server

2 Answers2