How Can I Split a Single Column of Multiple Values into Separate Columns in SQL?

Transforming a Single Column with Multiple Values into Separate Columns in SQL

Working with SQL data often involves the need to restructure columns. One common task is splitting a single column containing multiple values into distinct columns. Here's a method to achieve this:

The following SQL query uses CASE statements and string functions to parse a column named "name" containing first and last names:

<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 approach checks if the name column contains a space using LIKE '% %'. If a space exists, SUBSTRING and CHARINDEX functions extract the text before and after the space into first_name and last_name respectively. If no space is found, the entire name is assigned to first_name, and last_name is set to NULL.

This solution offers a practical approach for many common scenarios, although more complex situations might require more sophisticated string manipulation techniques.

The above is the detailed content of How Can I Split a Single Column of Multiple Values into Separate Columns in SQL?. For more information, please follow other related articles on the PHP Chinese website!