How to Turning a Comma Separated String into Individual Rows in SQL Server?
Given a column in the table having strings with comma-separated values. The task is to split all the comma-separated values and insert them as new rows.
So, let’s start by creating a database first.
Step 1: Create a Database.
Query :
CREATE DATABASE GFG
Step 2: Use the GFG Database.
Query :
USE GFG
Step 3: Create a table
Create a table (CommaSeparated) to store the data.
Query :
CREATE TABLE CommaSeparated(Strings VARCHAR(20));
Output :
Step 4: Insert some data into the database.
Query :
INSERT INTO CommaSeparated
VALUES ('A,C'),
('B'),
('A,G'),
('C'),
('B');
Output :
Step 5: SQL query to split all the comma-separated values and insert them as new rows.
We can split the string using STRING_SPLIT method. Get the new rows by applying the CROSS APPLY method to this.
Query :
the
VALUE
FROM
CommaSeparated CROSS APPLY STRING_SPLIT(Strings, ',');
Output :
Last Updated :
11 Apr, 2023
Like Article
Save Article
Share your thoughts in the comments
Please Login to comment...