SUBSTRING To split column name get first and last base on the space-CodePudding

I have NAME column data like

NAME
Victoria Brown
Sam Allen JR
Ray M James III

I want to split base on the number of space the firstname, lastname. HERE is what I did but last case statement is coming wrong it still getting the suffix when we have 3 space.

expecting:

NAME
Victoria Brown
Sam Allen
Ray James

SELECT
LEN (NAME ) - LEN (REPLACE (NAME, ' ', '')),
CASE 
    WHEN LEN(NAME) - LEN(REPLACE(NAME, ' ', '')) = 1 THEN SUBSTRING(NAME, 1, CHARINDEX(' ', NAME) - 1) 
    WHEN LEN(NAME) - LEN(REPLACE(NAME, ' ', '')) = 2 THEN SUBSTRING (NAME, CHARINDEX(' ', NAME, (CHARINDEX(' ', NAME)  1))  1, LEN(NAME)) 
    WHEN LEN(NAME) - LEN(REPLACE(NAME, ' ', '')) = 3 THEN SUBSTRING (NAME, 1, CHARINDEX(' ', NAME) - 1) 
END AS FIRSTNAME,

CASE 
    WHEN LEN(NAME) - LEN(REPLACE(NAME, ' ', '')) = 1 THEN SUBSTRING(NAME, CHARINDEX(' ', NAME)   1, LEN(NAME))
    WHEN LEN(NAME) - LEN(REPLACE(NAME, ' ', '')) = 2 THEN SUBSTRING (NAME, 1, CHARINDEX(' ', NAME) - 1)
    WHEN LEN(NAME) - LEN(REPLACE(NAME, ' ', '')) = 3 THEN SUBSTRING (NAME, CHARINDEX(' ', NAME, (CHARINDEX(' ', NAME)  1))  1, LEN(NAME) - CHARINDEX(' ', NAME, (CHARINDEX(' ', NAME)  1))  1) 
END AS LASTNAME

FROM INFOS

CodePudding user response：

If string_split is available in your version

select *
from infos
cross apply (
  select 
    max(case when rn = 1 then value else '' end) as firstname 
  , max(case 
        when parts = 2 and rn = 2 then value
        when parts > 2 and rn = parts - 1 then value
        else ''
        end) as lastname
  from 
  (
    select value
    , rn = row_number() over (order by (select null))
    , parts = count(*) over ()
    from string_split(name, ' ') spl
  ) q
) ca;

id	name	firstname	lastname
1	Victoria Brown	Victoria	Brown
2	Sam Allen JR	Sam	Allen
3	Ray M James III	Ray	James

Demo on db<>fiddle here