Parsing CSV or other delimited strings in SQL Server

In this script we will look at various ways we can design a string split function in SQL Server to split a delimited string in to individual rows.

We will use the following csv delimited string for building our split function.

Method 1 : USING charindex function

In this method we will use the system string function charindex. We loop through the string looking for the delimiter and parse the string accordingly.

Sample Run

Method 2: Using Charindex and CTE

In this method we use the combination of CTE and Charindex.
Note this method will work for SQL Server 2005 onwards.

Sample Run:

Method 3: Using XML

Here we will first convert the csv into XML and then using XML functions to parse the string.
Note this method can be used on SQL Server 2005 onwards.

Sample Run