I have a list of names, such as: Doe John James, Doe Jane and Doe Henry Albert Sr

I need to split those out into the four component parts of the name (if the name has four parts) to put each of them in their own column (last name, first name, middle name, suffix), while allowing for names that may or may not have a middle name or suffix.

I've got it figured out for everything but the third instance, where there is a suffix.

For first name I have:

    when LEN(LTRIM(rtrim(@name))) - LEN(replace(ltrim(rtrim(@name)),' ','')) = 2
        then LEFT(SUBSTRING(LTRIM(RTRIM(@name)), CHARINDEX(' ', LTRIM(RTRIM(@name))) + 1, LEN(LTRIM(RTRIM(@name)))), CHARINDEX(' ', SUBSTRING(LTRIM(RTRIM(@name)), CHARINDEX(' ', LTRIM(RTRIM(@name))) + 2, LEN(LTRIM(RTRIM(@name))))))
        SUBSTRING(@name, CHARINDEX(' ', @name) + 1, LEN(@name))
end as firstName

It's pretty crazy, but it works...

For middle name I have:

    when LEN(LTRIM(rtrim(@name))) - LEN(replace(ltrim(rtrim(@name)),' ','')) = 2
    else ''
end as middleName

Not quite as complex as the first name.

Since the last name is at the beginning that's the easiest:

SUBSTRING(LTRIM(RTRIM(@name)),1,CHARINDEX(' ',LTRIM(RTRIM(@name)))-1) as lastName

But I can't figure out how to allow for names with a suffix (Jr, Sr, III, etc).


2 Answers 2


Does something like this help you:

With split(id, n, start, pos) as(
    SELECT id, 1, 0, CHARINDEX(' ', fullname) FROM @names
    SELECT n.id, n+1, pos+1, CHARINDEX(' ', fullname, pos+1) 
    FROM @names n
    INNER JOIN split s ON n.id = s.id
    WHERE CHARINDEX(' ', fullname, start+1) > 0
    , lastName = MAX(CASE WHEN v.n = 1 THEN v.val END)
    , firstname = MAX(CASE WHEN v.n = 2 THEN v.val END)
    , middleName = MAX(CASE WHEN v.n = 3 THEN v.val END)
    , title = MAX(CASE WHEN v.n = 4 THEN v.val END)
    SELECT n.id, s.n
        , SUBSTRING(n.fullname
                , s.start
                , CASE WHEN s.pos = 0 THEN LEN(n.fullname)+1 ELSE s.pos END -s.start
    FROM @names n
    INNER JOIN split s ON n.id = s.id
) as v(id, n, val)
  • The recursive CTE get the start and end position of each name along with a value n from 1 to up to 4.
  • Based on the value of n the GROUP BY put them in their respective columns.

On a big table, it may not be very efficient though.


id  lastName    firstname   middleName  title
0   Doe         John        James       NULL
1   Doe         Jane        NULL        NULL
2   Doe         Henry       Albert      Sr
3   Bart        Simpson     NULL        NULL


declare @names table(id int identity(0, 1), fullname varchar(200), first varchar(50), middle varchar(50), last varchar(50), title varchar(50));
INSERT INTO @names(fullname) values
    ('Doe John James')
    , ('Doe Jane')
    , ('Doe Henry Albert Sr')
    , ('Bart Simpson')

You may like to first split the words and then apply your logic to separate the names. You can visit the following link for a custom split function:

T SQL Table Valued Function to Split a Column on commas

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.