How to Split a Single Column of Names into First and Last Name Columns in SQL?

Split a single column value into multiple column values

Question:

You want to convert a single column containing a list of names into multiple columns of first and last names.

Example:

<code class="language-sql">姓名
------------
abcd efgh
ijk lmn opq
asd j. asdjja
asb (asdfas) asd
asd</code>

Expected output:

<code class="language-sql">名             姓
----------------------------------
abcd                     efgh
ijk                      lm opq
asd                      asdjja
asb                      asd
asd                      NULL</code>

Solution:

The previous method may not handle all name formats correctly. Here is an improved solution using T-SQL:

<code class="language-sql">SELECT 
  CASE
    WHEN name LIKE '% %' THEN SUBSTRING(name, 1, CHARINDEX(' ', name) - 1)
    ELSE name
  END AS first_name,
  CASE
    WHEN name LIKE '% %' THEN SUBSTRING(name, CHARINDEX(' ', name) + 1, LEN(name))
    ELSE NULL
  END AS last_name
FROM 
  YourTable;</code>

This SQL code first checks for the presence of spaces in the name. If present, it uses the SUBSTRING function to extract the part before the space as the first name and the part after the space as the last name. If there are no spaces in the name, the entire name is used as the first name and the last name is NULL. This is more robust than the previous solution and handles various name formats better.

The above is the detailed content of How to Split a Single Column of Names into First and Last Name Columns in SQL?. For more information, please follow other related articles on the PHP Chinese website!