I have a SQL Server table with these columns:

ID, Name, FirstName, LastName

All names are stored in the Name column right now and I want to run an update to get the FirstName and LastName columns populated.

A sample of the current table looks like this:

1 | John Doe | NULL | NULL

After my update I expect this:

1 | John Doe | John | Doe

For simplicity's sake the following assumptions can be made:

• The Name column always contains the first and last name separated by a space.
• The first and last names are always one-word names, so there are no additional spaces.

I have tried the following (as a SELECT first so I can see the output before I run an UPDATE):

SELECT
    (SELECT TOP 2 value FROM STRING_SPLIT([NAME], ' ')) AS FirstName,   
    (SELECT TOP 1 value FROM STRING_SPLIT([NAME], ' ')) AS LastName 
FROM
    UserTable 
WHERE 
    LastName IS NULL
    AND [NAME] IS NOT NULL

However this does not work since the first column will return the first two top entries. But I want to get the second entry, not the first two. The second column (top 1) would work just fine.

I saw that STRING_SPLIT offers a third parameter -> ordinal. This would allow me to add a WHERE clause to the subquery. However this is only available in Azure but I am running SQL Server 2016 (v13) at the moment (no Azure).

What is the best way to achieve my UPDATE in SQL Server 2016?

CodePudding user response：

Given the specific constraints you specified:

You can use a combination of SUBSTRING and CHARINDEX

• Note the use of CROSS APPLY (VALUES to allow reuse of the CHARINDEX calculation
• Note the use of NULLIF to prevent errors in case there is no space

SELECT
  SUBSTRING(t.NAME, 1, v.space - 1) AS FirstName,
  SUBSTRING(t.NAME, v.space   1, LEN(t.NAME)) AS LastName
FROM UserTable t
CROSS APPLY (VALUES(
    NULLIF(CHARINDEX(' ', t.NAME), 0)
)) v(space)
WHERE t.LastName IS NULL
  AND t.[NAME] IS NOT NULL;

Note that this is not by any means a general solution to splitting up names.

Names can be very varied, and may have surnames before first names, may not have a surname, may have multiple first or surnames etc. Do not make assumptions.

CodePudding user response：

Here is another method by using tokenization via built-in PARSENAME() function.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, Name VARCHAR(100), FirstName VARCHAR(50), LastName VARCHAR(50));
INSERT INTO @tbl (Name, FirstName, LastName)
VALUES
('John Doe', NULL, NULL),
('Anna Mac', NULL, NULL);
-- DDL and sample data population, end

SELECT ID
    , FirstName = PARSENAME(tokens, 2)
    , LastName = PARSENAME(tokens, 1)
FROM @tbl
    CROSS APPLY (SELECT REPLACE(Name, SPACE(1),'.')) AS t(tokens);

Output

 ---- ----------- ---------- 
| ID | FirstName | LastName |
 ---- ----------- ---------- 
|  1 | John      | Doe      |
|  2 | Anna      | Mac      |
 ---- ----------- ----------