SQL- Get the substring after first space and second space in separate columns

Question

I have a column as FullName containg FirstName, MiddleName, LastName in it.
For example:

FullName: Marilyn Kean Kirkland

I want to have 3 separate columns for FirstName, MiddleName and LastName from the FullName by taking a substring from it.

I am pulling the FirstName by using the code:

substring(c.LegalName, 1, CHARINDEX(' ', c.LegalName)) as 'First Name'

I am wondering how can I pull just the middle name which comes after first space and before second space?
Also, I want to pull the last name which comes after the second space?

This is a bad idea. Not every full name has two spaces in it, and of those that do, not all consist of a first, middle, and last name. (Not everyone has a middle name (or even a last name); many people have multiple first/middle/last names; first/middle/last names can contain internal spaces; additional suffixes can follow the last name; etc., etc., etc.) See http://www.w3.org/International/questions/qa-personal-names. — ruakh, Apr 01 '15 at 19:41
[`CHARINDEX`](https://msdn.microsoft.com/en-us/library/ms186323(v=sql.100).aspx) has a third parameter. Use it. — GSerg, Apr 01 '15 at 19:44
@ruakh I know it's a bad idea but this is how our client wants the data to look like. Do you know how can I pull the middle name that comes after first space and before second space and pull the last name that comes after second space? If there is no middle or last name, it should leave it as blank. Your help will be appreciated. — Geetanjali Sachdeva, Apr 01 '15 at 20:40
@GeetanjaliSachdeva: Your client has hired you because of your expertise. If you know that something is a bad idea, then your client should benefit from that knowledge. You should explain to him/her why this is a bad idea. — ruakh, Apr 02 '15 at 00:29

Gordon Linoff · Answer 1 · 2015-04-01T22:18:19.253

1

SQL Server doesn't have very good string manipulation functions. This is easier with subqueries:

select firstname,
       stuff(reverse(stuff(reverse(legalname), 1, len(lastname) + 1, '')),
             1, len(firstname) + 1, '')
from (select legalname,
             left(legalname, charindex(' ', legalname) - 1) as firstname,
             right(legalname, charindex(' ', reverse(legalname)) - 1) as lastname
      . . .
     ) c

However, I would be very careful, because not all people have three part names. And others have suffixes (JR, SR) and other complications.

edited Apr 01 '15 at 22:18

answered Apr 01 '15 at 19:49

Gordon Linoff

1,242,037
58
646
786

This query doesn't work, it throws the error: Msg 174, Level 15, State 1, Line 2 The reverse function requires 1 argument(s). Msg 102, Level 15, State 1, Line 9 Incorrect syntax near 'c'. – Geetanjali Sachdeva Apr 01 '15 at 20:37
replace the ... with `FROM `. – ughai Apr 02 '15 at 05:35

score 1 · Answer 2 · answered Apr 02 '15 at 05:33

You can try something like this.

;WITH c AS 
(
    SELECT 'Marilyn Kean Kirkland' AS legalname
    UNION ALL SELECT 'J Smith' AS legalname
)
SELECT
    SUBSTRING(legalname,1,space1) firstname,
    SUBSTRING(legalname,space1,space2 - space1 + 1) middlename,
    SUBSTRING(legalname,space2 + 1,totallength - space2) lastname
FROM
(
    SELECT
        legalname,
        CHARINDEX(' ',legalname) space1,
        LEN(legalname) - CHARINDEX(' ',REVERSE(legalname)) space2,
        LEN(legalname) as totallength
    FROM c
)c
GO

As noted above by other users as well,these scripts will work with user that only have 3 part or 2 part names.

SQL- Get the substring after first space and second space in separate columns

2 Answers2

Linked