How do I split a string into multiple rows in SQL?

For an instructor lead, in-depth look at learning SQL click below.

Learn SQL >>

When working with SQL, you may come across scenarios where you need to split a string into multiple rows. This might seem like a complex task, but SQL is fully equipped to handle it, and once you understand how, it becomes quite straightforward. Let’s take a closer look at how to achieve this.

Approach

We would need to use a combination of SQL built-in functions. For the sake of this tutorial, we are going to use Microsoft SQL server database. Depending on your SQL version, there might be multiple ways to achieve string splitting. Here, we will illustrate the STRING_SPLIT function and the XML method.

Example

Assume we have a string ‘Apple, Mango, Banana’ and we want to split it into multiple rows in SQL. Here is how we will achieve this.

1. STRING_SPLIT function (SQL Server 2016 and higher)

This code declares a variable (@fruit), assigns a string to it, and then splits the string based on the ‘,’ delimiter using the STRING_SPLIT function.

2. XML Method (for SQL versions lower than 2016)

In this method, we convert the string to XML format and use the nodes method to split the string. Here, serves as the delimiter.

Conclusion

And that’s it! By utilizing the built-in capabilities of SQL, splitting a string into multiple rows becomes a straightforward task. Remember that there may be other methods available depending on your SQL version and database. However, the STRING_SPLIT function and the XML method are widely applicable and essential tools to have in your SQL toolkit.

Keep practicing SQL queries and stay tuned for more useful SQL posts to hone your data manipulation skills.