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

Split a single column value into multiple columns

The task is to split the values ​​in a single "name" column into multiple columns, including first name and last name. You want to omit the middle name and display the resulting data in the "first_name" and "last_name" columns.

Customized solution

The suggested approach may not be suitable for handling various name formats. However, a custom SQL query can achieve this:

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

This query uses the Charindex function to find the position of the first space character in the Name column. If spaces are found, the name is split into first and last name parts. If no spaces are found, the entire name is assigned to the first name column and a NULL value is assigned to the last name column. The second parameter of the RIGHT function has been corrected to use LEN(name) to calculate the string length, ensuring that the last name is extracted correctly.

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