T-SQL STRING_SPLIT to get first and last item from array

Given someone's full name, I'm trying to use STRING_SPLIT to get the firstname and lastname.

From Microsoft, STRING_SPLIT is used like this:

DECLARE @tags NVARCHAR(400) = 'clothing,road,,touring,bike'

SELECT value
FROM STRING_SPLIT(@tags, ',')
WHERE RTRIM(value) <> '';

Ref: https://learn.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql

But value wasn't declared anywhere.

I've been playing with this "new to me" STRING_SPLIT function, but I can't get it to work as it seems like it should.

declare @fullname varchar(255), @first varchar(50), @last varchar(50);
set @fullname='Lisa Simpson Chespirito';
select @first, @last from string_split(@fullname, ' ');

But, my unexpected output is:

(No column name) | (No column name)
NULL             | NULL
NULL             | NULL

Aside from "Why aren't my values being assigned?", I'd also like to know why 2 rows are returned.

There is also the ordinal property:

If the enable_ordinal argument is passed a value of 1, a second column named ordinal is returned that consists of the 1-based index values of each substring's position in the input string. The return type is bigint.

Let's assume that value is a property that I can access. Can I somehow use ordinal to selectively pick the first and last items from value?

I'm thinking about something like this:

set @first=value(1);
set @last=value(value.length);

That isn't working, though.

How would I do this?

Solution

Only SQL Server 2022 provides the order of items. In previous versions you have to use string manipulation to retrieve specific items.

Finding the first item is easy: find the first , and take the characters up to it:

select left(@tags,charindex(',',@tags)-1) as first

There's no function that returns the last index of a character. One way to do this is to reverse the text before searching

select 
    left(@tags,charindex(',',@tags)-1) as first,
    RIGHT(@tags, CHARINDEX(',', REVERSE(@tags)) - 1) as last