{"id":427,"date":"2008-09-16T00:00:00","date_gmt":"2008-08-26T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/faking-arrays-in-transact-sql\/"},"modified":"2021-09-29T16:22:09","modified_gmt":"2021-09-29T16:22:09","slug":"faking-arrays-in-transact-sql","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/faking-arrays-in-transact-sql\/","title":{"rendered":"Faking Arrays in Transact SQL"},"content":{"rendered":"<ul>\n<li><a href=\"#Intro\">Introduction<\/a><\/li>\n<li><a href=\"#Consider\">Considerations<\/a><\/li>\n<li><a href=\"#Direct\">Direct Data comparison<\/a><\/li>\n<li><a href=\"#Parse\">String Parsing<\/a><\/li>\n<li><a href=\"#parsename\">Using the PARSENAME() function<\/a><\/li>\n<li><a href=\"#Numbers\">Using a table of numbers<\/a><\/li>\n<li><a href=\"#CreateNum\">Creating a Number(sequentially incrementing values) table<\/a><\/li>\n<li><a href=\"#Identity\">Identity bases sequences<\/a><\/li>\n<li><a href=\"#While\">Regular loops using WHILE<\/a><\/li>\n<li><a href=\"#ExistNum\">Sequences based on existing tables <\/a><\/li>\n<li><a href=\"#RowNum\">With ROW_NUMBER()<\/a><\/li>\n<li><a href=\"#CteNum\">Approaches using Common table expressions <\/a><\/li>\n<li><a href=\"#Num\">Parsing a delimited string using a Number Table<\/a><\/li>\n<li><a href=\"#Cte\">With Common Table Expressions<\/a><\/li>\n<li><a href=\"#Replace\">By replacing delimiters<\/a><\/li>\n<li><a href=\"#XML\">XML solutions<\/a><\/li>\n<li><a href=\"#openxml\">OPENXML <\/a><\/li>\n<li><a href=\"#nodes\">The nodes() method<\/a><\/li>\n<li><a href=\"#Loop\">Simple loop approach<\/a><\/li>\n<li><a href=\"#CLR\">Using a CLR\u00a0 Function<\/a><\/li>\n<li><a href=\"#Concl\">Conclusion<\/a><\/li>\n<li><a href=\"#Refer\">References<\/a><\/li>\n<li><a href=\"#Acknow\">Acknowledgements<\/a><\/li>\n<\/ul>\n<h2 id=\"Intro\">Introduction<\/h2>\n<p>Sometimes SQL programmers come up with a requirement to use multi-valued columns or variables in an SQL query, especially when the data comes from somewhere else. Since there is no built-in support for any such datatype as an array or list\u00a0 in t-SQL, quite often folks use delimited <b>VARCHAR<\/b> strings to lump multiple values together.<\/p>\n<p>We all know that having multiple values in a single column is against the fundamental tenet of relational model since an attribute in a relation can have only one value drawn from an appropriate type.<\/p>\n<p>SQL tables, by their very nature, do not allow multiple values in its columns. This is a sensible stricture which, even before the XML datatype appeared, programmers have occasionally been defeating with varying degrees of success. Users can interpret a single string as a combination of smaller strings usually delimited by a character such as\u00a0 a comma or a space. Therefore, by leveraging the existing string functions, programmers can extract such smaller parts from the concatenated string.<\/p>\n<p>It is very important to note that some of the methods that we&#8217;ll describe are kludgy, while others appear to violate the fundamentals of data independence altogether. Such methods are only suggested here only for the sake of completeness, and are not recommended for use in production systems. If you are using any methods that are undocumented in the product manual, use them with due caution and all relevant caveats apply. Basic recommendations for using proper datatype conversion techniques, avoiding positional significance for columns etc must be considered for stable production code.<\/p>\n<h2 id=\"Consider\">Considerations<\/h2>\n<p>Most of the methods of parsing an array and using it for data manipulation are used to insert data as multiple rows into tables. There are other occasions where the programmer has the task of cleaning and querying\u00a0 badly-designed data represented in a table.<\/p>\n<p>The following sections illustrate a variety of methods one can employ to identify and enlist subsections of a string represented in a variable, parameter or even as a column value in a table. The examples use a comma separated list, commonly known as a CSV string where the value is represented as: <br \/>\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 &#8216;item-1,item-2,item-3&#8230;.item-n&#8217;. <br \/>\n In practice, you can use any character including a space to delimit and improvise the methods accordingly.<\/p>\n<p>For the examples below, a few customer identifiers are randomly chosen from the Customers table in the <b>Northwind <\/b>database. For each example below, we&#8217;ll use this list.<\/p>\n<pre class=\"theme:ssms2012-simple-talk lang:tsql decode:true\">DECLARE @p VARCHAR(50)\r\nSET @p = 'ALFKI,LILAS,PERIC,HUNGC,SAVEA,SPLIR,LONEP,GROSR'<\/pre>\n<p><b>Northwind<\/b> is a sample database in SQL Server 2000 default installations. You can download a copy from the <a href=\"http:\/\/www.microsoft.com\/downloads\/details.aspx?familyid=06616212-0356-46a0-8da2-eebc53a68034&amp;displaylang=en\">Microsoft Downloads<\/a><\/p>\n<h2 id=\"Direct\">Direct Data comparison<\/h2>\n<p>For simple comparisons, there is no need for complicated routines. The inherent pattern matching features in Transact SQL can be used directly in most cases. One prime example is to pass in a list of values and use it in the IN list of a WHERE clause. Here are some common methods:<\/p>\n<p>Using the CHARINDEX function:<\/p>\n<pre class=\"theme:ssms2012-simple-talk lang:tsql decode:true\">\u00a0  SELECT CustomerID, ContactName, CompanyName\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0FROM Northwind.dbo.Customers\r\n\u00a0\u00a0\u00a0\u00a0 WHERE CHARINDEX( ',' + CustomerID + ',', ',' + @p + ',' ) &gt; 0 ;\r\n\u00a0\u00a0\u00a0\u00a0 \r\n\/*\r\n\u00a0\u00a0\u00a0\u00a0CustomerID ContactName\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0CompanyName\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\r\n\u00a0\u00a0\u00a0\u00a0---------- ------------------------------ ------------------------------\r\n\u00a0\u00a0\u00a0\u00a0ALFKI\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Maria Anders\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Alfreds Futterkiste\r\n\u00a0\u00a0\u00a0\u00a0GROSR\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Manuel Pereira\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 GROSELLA-Restaurante\r\n\u00a0\u00a0\u00a0\u00a0HUNGC\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Yoshi Latimer\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Hungry Coyote Import Store\r\n\u00a0\u00a0\u00a0\u00a0LILAS\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Carlos Gonz\u00c3\u00a1lez\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0LILA-Supermercado\r\n\u00a0\u00a0\u00a0\u00a0LONEP\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Fran Wilson\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Lonesome Pine Restaurant\r\n\u00a0\u00a0\u00a0\u00a0PERIC\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Guillermo Fern\u00c3\u00a1ndez\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Pericles Comidas cl\u00c3\u00a1sicas\r\n\u00a0\u00a0\u00a0\u00a0SAVEA\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Jose Pavarotti\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Save-a-lot Markets\r\n\u00a0\u00a0\u00a0\u00a0SPLIR\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Art Braunschweiger\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Split Rail Beer &amp; Ale\r\n\r\n\u00a0\u00a0\u00a0\u00a0(8 row(s) affected)*\/\r\n<\/pre>\n<p>Using pattern matching with PATINDEX:<\/p>\n<pre class=\"theme:ssms2012-simple-talk lang:tsql decode:true\">\u00a0\u00a0\u00a0\u00a0SELECT CustomerID, ContactName, CompanyName\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0FROM Northwind.dbo.Customers\r\n\u00a0\u00a0\u00a0\u00a0 WHERE PATINDEX( '%,' + CustomerID + ',%', ',' + @p + ',' ) &gt; 0\u00a0\u00a0 ; <\/pre>\n<p>Using LIKE operator for pattern matching<\/p>\n<pre class=\"theme:ssms2012-simple-talk lang:tsql decode:true\">\u00a0\u00a0\u00a0\u00a0SELECT CustomerID, ContactName, CompanyName\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0FROM Northwind.dbo.Customers\r\n\u00a0\u00a0\u00a0\u00a0 WHERE ',' + @p + ',' LIKE '%,' + CustomerID + ',%' ;<\/pre>\n<h2 id=\"Parse\">String Parsing<\/h2>\n<p>In many cases, you may want to use the parsed list of values as a resultset that can be used in subsequent operations. For instance, for larger lists, it may be more effective to use a JOIN rather using an IN() clause. Another common scenario is the case of multi-row inserts where the list is parsed and the individual elements are inserted using a single INSERT statement.<\/p>\n<h3 id=\"parsename\">Using the PARSENAME() function<\/h3>\n<p>The PARSENAME function returns a SYSNAME value (equivalent to NVARCHAR(128) and is generally used for identifying part of a database object like object name, owner name, database name, and server name. However it can also be used for parsing a string if the string is made up of less than five delimited values. Typical scenarios for applying this approach would be splitting up a full name, identifying parts of an IP address etc.<\/p>\n<p>For example:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">DECLARE @FullName NVARCHAR(500) \r\nSET @FullName = N'Flintstone, Mr.Fred, Jr' \r\nSELECT PARSENAME(FName, 3) AS \"Title\", \r\n\u00a0\u00a0\u00a0 \u00a0\u00a0 PARSENAME(FName, 2) AS \"FirstName\", \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 PARSENAME(FName, 4) AS \"LastName\", \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 PARSENAME(FName, 1) AS \"Prefix\"\r\n\u00a0 FROM ( SELECT REPLACE(@FullName, ', ', '.')) D(FName) \r\n<\/pre>\n<h3 id=\"Numbers\">Solutions using a table of numbers<\/h3>\n<p>In most cases with larger strings, the faster solutions are often the ones using a table of sequentially incrementing numbers. However, the performance assertions in general should be taken with a grain of salt since, without testing, it is almost impossible to conclude which method performs better than another.\u00a0 Before we go through the various solutions that use a table of numbers we&#8217;ll run through a few of the approaches to creating a number table.<\/p>\n<h4 id=\"CreateNum\">Creating a Number (Sequentially incrementing values) table in T-SQL<\/h4>\n<div class=\"indent\">\n<p>A table of monotonically increasing numbers can be created in a variety of ways. Either a base table or a view or any expression that can create a sequence of numbers can be used in these scenarios. Some of the common methods to create a table of numbers \u00a0are detailed in the next section.<\/p>\n<p>Though sequentially incrementing values can be generated as a part of the query, generally, it is recommended that you create a permanent base table and insert as many numbers as you need for various solutions. It is also advised to make sure the number column is set as a Primary Key to avoid any potential duplication of rows.<\/p>\n<div class=\"indent\">\n<h5 id=\"Identity\">Identity based sequences<\/h5>\n<p>Using the default values for an Identity Column in a table, one can simply insert default rows to generate the sequence. One consideration in this approach is that it can be used only with permanent base tables. Note that without any arguments IDENTITY property uses the value 1 for both seed and increment.<\/p>\n<pre class=\"theme:ssms2012-simple-talk lang:tsql decode:true\">\u00a0\u00a0\u00a0\u00a0CREATE TABLE dbo.Nbrs(n INT NOT NULL IDENTITY) ;\r\n\u00a0\u00a0\u00a0\u00a0GO\r\n\u00a0\u00a0\u00a0\u00a0SET NOCOUNT ON ;\r\n\u00a0\u00a0\u00a0\u00a0INSERT dbo.Nbrs DEFAULT VALUES ;\r\n\u00a0\u00a0\u00a0\u00a0WHILE SCOPE_IDENTITY() &lt; 500\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0INSERT dbo.Nbrs DEFAULT VALUES ;<\/pre>\n<p>With the IDENTITY() function we can use SELECT INTO a new table from any existing table. This example demonstrates using a CROSS JOIN between two existing tables.<\/p>\n<pre class=\"theme:ssms2012-simple-talk lang:tsql decode:true\">\u00a0\u00a0\u00a0\u00a0SELECT TOP 500 IDENTITY(INT) AS n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0INTO dbo.Nbrs\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0FROM Northwind.dbo.Orders o1\r\n\u00a0\u00a0\u00a0\u00a0 CROSS JOIN Northwind.dbo.Orders o2 ;<\/pre>\n<h5 id=\"While\">Regular loops using WHILE<\/h5>\n<p>Here is a simple procedural loop with inserting one row at a time.<\/p>\n<pre class=\"theme:ssms2012-simple-talk lang:tsql decode:true\">CREATE TABLE dbo.Nbrs(n INT NOT NULL PRIMARY KEY) ;\r\n\u00a0\u00a0\u00a0\u00a0GO\r\n\u00a0\u00a0\u00a0\u00a0SET NOCOUNT ON\r\n\u00a0\u00a0\u00a0\u00a0DECLARE @Index INT ;\r\n\u00a0\u00a0\u00a0\u00a0SET @Index = 1 ;\r\n\u00a0\u00a0\u00a0\u00a0WHILE @Index &lt;= 500 BEGIN \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0INSERT dbo.Nbrs (n) VALUES (@Index) ;\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0SET @Index = @Index + 1 ;\r\n\u00a0\u00a0\u00a0\u00a0END <\/pre>\n<p>The efficiency of the above loop ca be improved using the following suggestion from a newsgroup posting by Itzik Ben-Gen.<\/p>\n<pre class=\"theme:ssms2012-simple-talk lang:tsql decode:true\">\u00a0\u00a0\u00a0\u00a0SET NOCOUNT ON \r\n\u00a0\u00a0\u00a0\u00a0DECLARE @Index AS INT;\r\n\u00a0\u00a0\u00a0\u00a0SET @Index = 1 \r\n\r\n\u00a0\u00a0\u00a0\u00a0INSERT dbo.Nbrs SELECT 1;\r\n\u00a0\u00a0\u00a0\u00a0WHILE @Index * 2 &lt; 500 BEGIN \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0INSERT dbo.Nbrs \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0SELECT @Index + n \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM dbo.Nbrs\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0SET @Index = @Index + @@ROWCOUNT \r\n\u00a0\u00a0\u00a0\u00a0END \r\n\u00a0\u00a0\u00a0\u00a0INSERT dbo.Nbrs \r\n\u00a0\u00a0\u00a0\u00a0SELECT @Index + n \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0FROM dbo.Nbrs \r\n\u00a0\u00a0\u00a0\u00a0 WHERE @Index + n &lt;= 500<\/pre>\n<h5 id=\"ExistNum\">Sequences based on existing tables<\/h5>\n<p>Using an existing base table to view can be an easy way to generate a sequence of numbers, esp. when you don&#8217;t want to create a permanent table to support a single querty. One drawback to such mechanisms is that as the dataset gets larger, the comparative efficiency of the inserts can become an issue. <\/p>\n<p> Here is an example using a correlated subquery based on the unique column.<\/p>\n<pre class=\"theme:ssms2012-simple-talk lang:tsql decode:true\">\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0SELECT n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0FROM ( SELECT ( SELECT COUNT(*)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0FROM Northwind.dbo.Orders o2\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHERE o2.OrderId &lt;= o1.OrderId ) AS \"n\"  \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM Northwind.dbo.Orders o1 ) Nbrs (n)\r\n\u00a0\u00a0\u00a0WHERE n &lt;= 500 ;\r\n<\/pre>\n<p>This is the same logic as above, but using a self join<\/p>\n<pre class=\"theme:ssms2012-simple-talk lang:tsql decode:true\">\u00a0\u00a0\u00a0\u00a0SELECT COUNT(*) AS \"n\" \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0FROM Northwind.dbo.Orders o1\r\n\u00a0\u00a0\u00a0\u00a0 INNER JOIN Northwind.dbo.Orders o2\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0ON o2.OrderId &lt;= o1.OrderId\r\n\u00a0\u00a0\u00a0\u00a0 GROUP BY o1.OrderId \r\n\u00a0\u00a0\u00a0\u00a0HAVING COUNT(*) &lt;= 500 ;<\/pre>\n<p class=\"MsoNormal\">Another quick way to generate the sequence is to use the ROW_NUMBER() function. This example uses a CROSS JOIN just to illustrate the ease of formulation<\/p>\n<pre class=\"theme:ssms2012-simple-talk lang:tsql decode:true\">\u00a0\u00a0\u00a0\u00a0WITH Nbrs ( n ) AS (\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT ROW_NUMBER() OVER (ORDER BY n) AS n \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM ( SELECT s1.id \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM Northwind.dbo.Orders o1\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CROSS JOIN Northwind.dbo.Orders o2 ) D ( n )\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0) SELECT n \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0FROM Nbrs\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHERE n BETWEEN 1 AND 500 ;<\/pre>\n<h5 id=\"RowNum\">With ROW_NUMBER()<\/h5>\n<p>For each row returned in a resultset, ROW_NUMBER function returns a sequential number, starting at 1. One can use any existing table, view or any resultset to generate a ROW_NUMBER() expression. Here is an example: \u00a0<\/p>\n<pre class=\"theme:ssms2012-simple-talk lang:tsql decode:true\">\u00a0\u00a0\u00a0\u00a0SELECT n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY OrderId )\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM Northwind.dbo.Orders ) D ( n )\r\n\u00a0\u00a0\u00a0\u00a0 WHERE n &lt;= 500 ;<\/pre>\n<p>As a matter of fact, you can use any arbitrary expression or built-in functions like CURRENT_TIMESTAMP or NEW_ID() in the ORDER BY clause to generate the sequential number.<\/p>\n<h5 id=\"CteNum\">Approaches using Common table expressions<\/h5>\n<p>Common table expressions are aliased resultsets derived from a simple query and defined within the execution scope of a single DML statement. A CTE, with an alias, optional column list and a definition query, can help simplify complex queries and improve readability. In general, any query can be written as a CTE query, but there are certain instances where a query CTE construct can offer the best performing query.<\/p>\n<p>Using a CTE, some of the above methods for generating a sequence of numbers can be simplified.<\/p>\n<p>Here is a simple transformation of derived table of unit digits. The logic is pretty much self explanatory.<\/p>\n<pre class=\"theme:ssms2012-simple-talk lang:tsql decode:true\">\u00a0\u00a0\u00a0\u00a0 WITH Units ( nbr ) AS ( \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0SELECT 9 )\r\n\u00a0\u00a0\u00a0\u00a0SELECT u3.nbr * 100 + u2.nbr * 10 + u1.nbr + 1 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0FROM Units u1, Units u2, Units u3 \r\n\u00a0\u00a0\u00a0\u00a0 WHERE u3.nbr * 100 + u2.nbr * 10 + u1.nbr + 1 &lt;= 500 ;<\/pre>\n<p>A Recursive CTE is common table expression can include references to itself. With an anchor member and a recursive member, a recursive CTE can generate a number listing pretty efficiently. \u00a0Also note the default recursion limit can be changed by using the OPTION (MAXRECURSION) to the desired limit (0 denotes unlimited).<\/p>\n<pre class=\"theme:ssms2012-simple-talk lang:tsql decode:true\">\u00a0\u00a0\u00a0\u00a0 WITH Nbrs ( n ) AS (\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0SELECT 1 UNION ALL\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0SELECT 1 + n FROM Nbrs WHERE n &lt; 500 )\r\n\u00a0\u00a0\u00a0\u00a0SELECT n FROM Nbrs\r\n\u00a0\u00a0\u00a0\u00a0OPTION ( MAXRECURSION 500 )<\/pre>\n<p>Here is a clever method again by Itzik Ben-Gen using a CROSS JOIN of nested CTE references. This is not only quick but can be used for generating larger datasets.<\/p>\n<pre class=\"theme:ssms2012-simple-talk lang:tsql decode:true\">\u00a0\u00a0\u00a0\u00a0 WITH Nbrs_3( n ) AS ( SELECT 1 UNION SELECT 0 ),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Nbrs_2( n ) AS ( SELECT 1 FROM Nbrs_3 n1 CROSS JOIN Nbrs_3 n2 ),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Nbrs_1( n ) AS ( SELECT 1 FROM Nbrs_2 n1 CROSS JOIN Nbrs_2 n2 ),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Nbrs_0( n ) AS ( SELECT 1 FROM Nbrs_1 n1 CROSS JOIN Nbrs_1 n2 ),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Nbrs\u00a0\u00a0( n ) AS ( SELECT 1 FROM Nbrs_0 n1 CROSS JOIN Nbrs_0 n2 )\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0SELECT n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0FROM ( SELECT ROW_NUMBER() OVER (ORDER BY n)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM Nbrs ) D ( n )\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHERE n &lt;= 500 ;<\/pre>\n<\/div>\n<h4 id=\"Num\">Parsing a delimited string using a Number Table<\/h4>\n<p>Now we have our number table, we can start using it a number of different ways to split fake arrays. The general idea here is to use the sequence value as the second argument for the SUBSTRING() function and then use the WHERE clause to limit the number of splits using a terminating criteria. Note than this general logic can be written in a variety of ways.<\/p>\n<pre class=\"theme:ssms2012-simple-talk lang:tsql decode:true\">\u00a0\u00a0\u00a0\u00a0SELECT SUBSTRING( ',' + @p + ',', n + 1, \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0CHARINDEX( ',', ',' + @p + ',', n + 1 ) - n - 1 ) AS \"value\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0FROM Nbrs\r\n\u00a0\u00a0\u00a0\u00a0 WHERE SUBSTRING( ',' + @p + ',', n, 1 ) = ','\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND n &lt; LEN( ',' + @p + ',' ) ;<\/pre>\n<p>Similar logic from a query in a public newsgroup posting by Linda Wiezbecki.<\/p>\n<pre class=\"theme:ssms2012-simple-talk lang:tsql decode:true\">\u00a0\u00a0\u00a0\u00a0SELECT SUBSTRING( p, n + 1, \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0CHARINDEX( ',', p, n + 1 ) - ( n + 1 ) ) AS \"value\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0FROM Nbrs\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0JOIN ( SELECT ',' + @p + ',' ) D ( p )\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0ON SUBSTRING( p, n, LEN( p ) ) LIKE ',_%' ;\r\n<\/pre>\n<p>Here are a couple of methods that not only parses the delimited list, but returns the position of each item in the list as well. This one is using a self join and is popularized by Joe Celko in SQL newsgroups. postings.<\/p>\n<pre class=\"theme:ssms2012-simple-talk lang:tsql decode:true\">\u00a0\u00a0\u00a0\u00a0SELECT SUBSTRING( ',' + @p + ',', MAX( n1.n + 1 ),\u00a0\u00a0\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0n2.n - MAX( n1.n + 1 ) ) AS \"value\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 COUNT( n2.n ) AS \"pos\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0FROM Nbrs n1\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0JOIN Nbrs n2 ON n1.n &lt; n2.n \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND n2.n &lt;= LEN( ',' + @p + ',' ) + 1 \r\n\u00a0\u00a0\u00a0\u00a0 WHERE SUBSTRING( ',' + @p + ',', n1.n, 1 ) = ','\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND SUBSTRING( ',' + @p + ',', n2.n, 1 ) = ','\r\n\u00a0\u00a0\u00a0\u00a0 GROUP BY n2.n ;<\/pre>\n<p>The following is from a posting by Umachandar Jayachandran that uses the REPLACE function to get the number of commas in the string and calculate the position of each item value.<\/p>\n<pre class=\"theme:ssms2012-simple-talk lang:tsql decode:true\">\u00a0\u00a0\u00a0\u00a0SELECT SUBSTRING( p, n + 1, CHARINDEX( ',', p, n + 1 ) - n - 1 ) AS \"value\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 LEN( SUBSTRING( p, 1, CHARINDEX( ',', p, n + 1 ) ) ) - \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 LEN( REPLACE( SUBSTRING( p, 1, \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0CHARINDEX( ',', p, n + 1 ) ), ',', '' ))\u00a0\u00a0- 1 AS \"pos\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0FROM Nbrs\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0JOIN ( SELECT ',' + @p + ',' ) D ( p ) \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0ON n BETWEEN 1 AND LEN( p ) - 1 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND SUBSTRING( p, n , 1 ) = ',' ;<\/pre>\n<p>The following method also gives you the positional value and is simpler<\/p>\n<pre class=\"theme:ssms2012-simple-talk lang:tsql decode:true\">\u00a0\u00a0\u00a0\u00a0SELECT SUBSTRING( @p, n, CHARINDEX( ',', @p + ',', n ) - n ) AS \"value\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 n + 1 - LEN( REPLACE( LEFT( @p, n ), ',', '' ) ) AS \"pos\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0FROM Nbrs\u00a0\u00a0\r\n\u00a0\u00a0\u00a0\u00a0 WHERE SUBSTRING(',' + @p, n, 1) = ','\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND n &lt; LEN(@p) + 1 ;<\/pre>\n<p>With the <b>ROW_NUMBER() <\/b>and <b>RANK()<\/b> functions introduced in SQL 2005, one could create the positional values much easily like:<\/p>\n<pre class=\"theme:ssms2012-simple-talk lang:tsql decode:true\">\u00a0\u00a0\u00a0\u00a0SELECT SUBSTRING( ',' + @p + ',', n + 1, \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0CHARINDEX( ',', ',' + @p + ',', n + 1 ) - n - 1 ) AS \"value\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ROW_NUMBER() OVER ( ORDER BY n ) AS \"pos\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0FROM Nbrs\r\n\u00a0\u00a0\u00a0\u00a0 WHERE SUBSTRING( ',' + @p + ',', n, 1 ) = ','\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND n &lt; LEN( ',' + @p + ',' ) ;<\/pre>\n<p>You can wrap any of these methods using a table of sequentially incrementing numbers into a table valued UDF or another stored procedure and make it more generic, reusable and handy.<\/p>\n<\/div>\n<h3 id=\"Cte\">With Common Table Expressions<\/h3>\n<p>Most of the methods described above can be re-written using common table expressions. Here is one way of using it to get the starting position and ending position of each element item and then using <b>SUBSTRING<\/b> to extract the value:<\/p>\n<pre class=\"theme:ssms2012-simple-talk lang:tsql decode:true\">\u00a0\u00a0\u00a0\u00a0WITH CTE ( pos_begin, pos_end ) AS (\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0SELECT n1.n, ( SELECT MIN( n2.n )FROM Nbrs n2\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0WHERE SUBSTRING( ',' + @p + ',' , n2.n , 1 ) = ',' \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0AND n2.n &gt; n1.n ) \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0FROM Nbrs n1\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHERE n1.n &lt;= LEN( ',' + @p + ',' ) - 1 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND SUBSTRING( ',' + @p + ',' , n1.n , 1 ) = ',' )\r\n\u00a0\u00a0\u00a0\u00a0SELECT SUBSTRING( @p , pos_begin, pos_end - pos_begin - 1 ) AS \"Value\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0FROM CTE ;<\/pre>\n<p>Another concise approach with Common table expressions is to use a recursive CTE. It does not use a table of sequentially incrementing numbers, however it can be a bit inefficient for larger strings.<\/p>\n<p>Here is an example.<\/p>\n<pre class=\"theme:ssms2012-simple-talk lang:tsql decode:true\">\u00a0\u00a0\u00a0\u00a0WITH CTE ( pos, pos_begin, pos_end ) AS (\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT 0, 1, CHARINDEX( ',', @p + ',' ) \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0UNION ALL\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT pos + 1, pos_end + 1, CHARINDEX( ',', @p + ',', pos_end + 1 ) \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM CTE\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0WHERE CHARINDEX( ',', @p + ',', pos_end + 1 ) &gt; 0 )\r\n\u00a0\u00a0\u00a0\u00a0SELECT pos + 1, SUBSTRING( @p, pos_begin , pos_end - pos_begin ) AS \"value\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0FROM CTE\r\n\u00a0\u00a0\u00a0\u00a0OPTION ( MAXRECURSION 0 ) ;<\/pre>\n<p>A similar approach is suggested by Hari Mohan, a systems analyst with BFL software:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">;WITH cte (pos_begin, pos_end) AS\r\n(\u00a0\u00a0 SELECT 0, 1\r\n\u00a0\u00a0\u00a0\u00a0 UNION ALL\r\n\u00a0\u00a0\u00a0 SELECT pos_end, charindex(',', @p, pos_end) + 1\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 FROM cte\r\n\u00a0\u00a0\u00a0\u00a0 WHERE pos_end &gt; pos_begin )\r\nSELECT SUBSTRING(@p, pos_begin,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0 CASE WHEN pos_end &gt; 1 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0 THEN pos_end - pos_begin - 1 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0 ELSE LEN(@p) - pos_begin + 1 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 END ) AS \"value\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 RANK() OVER ( ORDER BY pos_begin )\u00a0\u00a0\u00a0\u00a0 \r\n\u00a0 FROM cte \r\n\u00a0WHERE pos_begin &gt; 0 \r\nOPTION ( MAXRECURSION 0 ) ;\r\n\r\n<\/pre>\n<p>As mentioned before, any of the above mentioned parsing routines can be written as a table valued user defined function or a view and can be used directly in the queries like:<\/p>\n<pre class=\"theme:ssms2012-simple-talk lang:tsql decode:true\">\u00a0\u00a0\u00a0\u00a0SELECT CustomerID, ContactName, CompanyName\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0FROM Northwind.dbo.Customers c\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0JOIN dbo.udf_parsed_list () p\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0ON c.CustomerID = p.value ;<\/pre>\n<h3 id=\"Replace\">Methods that replace the delimiters<\/h3>\n<p>Apart from the above mentioned approaches there are certain other tricks which can be used in Transact SQL. Basically the following methods uses Dynamic SQL, a bit different from traditional queries, but can be used as an approach for smaller string parsing requirements in certain cases.<\/p>\n<p>Here is an example using Dynamic SQL with <b>IN<\/b> list in the <b>WHERE<\/b> clause<\/p>\n<pre class=\"theme:ssms2012-simple-talk lang:tsql decode:true\">\u00a0\u00a0\u00a0\u00a0DECLARE @SQLx NVARCHAR(4000)\r\n\u00a0\u00a0\u00a0\u00a0SET @SQLx = N'\r\n\u00a0\u00a0\u00a0\u00a0SELECT CustomerID, ContactName, CompanyName\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0FROM Northwind.dbo.Customers\u00a0\u00a0\r\n\u00a0\u00a0\u00a0\u00a0 WHERE CustomerID IN ( ''' + REPLACE( @p, N',', N''',''' ) + N''' )' ;\r\n\u00a0\u00a0\u00a0\u00a0EXEC sp_ExecuteSQL @SQLx ;<\/pre>\n<p>This is another illustration of replacing the element delimiters with &#8216; <b>UNION SELECT<\/b> &#8216;. The results can be stored in a table variable or table variable and then can be used in subsequent operations.<\/p>\n<pre class=\"theme:ssms2012-simple-talk lang:tsql decode:true\">\u00a0\u00a0\u00a0\u00a0DECLARE @tbl TABLE ( val VARCHAR(10) NOT NULL PRIMARY KEY );\r\n\u00a0\u00a0\u00a0\u00a0DECLARE @SQLx VARCHAR(8000)\r\n\u00a0\u00a0\u00a0\u00a0SET @SQLx = 'SELECT ''' + REPLACE( @p, ',', ''' UNION SELECT ''') + ''''\r\n\u00a0\u00a0\u00a0\u00a0INSERT @tbl EXEC( @SQLx ) ;\r\n\u00a0\u00a0\u00a0\u00a0SELECT val FROM @tbl ;\r\n\u00a0<\/pre>\n<p>A similar approach is proposed by Alejandro Mesa in a public newsgroup posting that uses separate INSERT statements as well. Here is an example where @t is a valid table created prior to execution:<\/p>\n<pre class=\"theme:ssms2012-simple-talk lang:tsql decode:true\">\u00a0\u00a0\u00a0\u00a0DECLARE @SQLx VARCHAR(8000)\r\n\u00a0\u00a0\u00a0\u00a0SET @SQLx= 'INSERT ' + @t + ' VALUES (' +\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 REPLACE( @p, ',', ' ) INSERT ' + @t + ' VALUES (') + ')'\r\n\u00a0\u00a0\u00a0\u00a0EXEC ( @SQLx ) ;<\/pre>\n<h3 id=\"XML\">XML solutions<\/h3>\n<h4 id=\"Openxml\">OPENXML function<\/h4>\n<p>An approach that is getting much attention, is the <b>OPENXML <\/b>method. This method, thought may not be ideal for larger datasets, can be effectively used for relatively small number of items, especially if the XML string is generated from a client application. The basic idea is to pass the values as an XML document to the stored procedure instead of a CSV. Here is an example. Note that you can use the <b>REPLACE<\/b> function to change a CSV string to XML format, say within a stored procedure for instance.<\/p>\n<pre class=\"theme:ssms2012-simple-talk lang:tsql decode:true\">\u00a0\u00a0\u00a0\u00a0DECLARE @doc VARCHAR(500)\r\n\u00a0\u00a0\u00a0\u00a0DECLARE @XMLDoc INT\r\n\u00a0\u00a0\u00a0\u00a0SET @doc = '\r\n\u00a0\u00a0\u00a0\u00a0&lt;ROOT&gt;\r\n\u00a0\u00a0\u00a0\u00a0&lt;Customer pos=\"1\" id=\"ALFKI\"\"\"&gt;&lt;\/Customer&gt;\r\n\u00a0\u00a0\u00a0\u00a0&lt;Customer pos=\"2\" id=\"LILAS\"\"&gt;&lt;\/Customer&gt;\r\n\u00a0\u00a0\u00a0\u00a0&lt;Customer pos=\"3\" id=\"PERIC\"\"\"&gt;&lt;\/Customer&gt;\r\n\u00a0\u00a0\u00a0\u00a0&lt;Customer pos=\"4\" id=\"HUNGC\"\"\"&gt;&lt;\/Customer&gt;\r\n\u00a0\u00a0\u00a0\u00a0&lt;Customer pos=\"5\" id=\"SAVEA\"\"\"&gt;&lt;\/Customer&gt;\r\n\u00a0\u00a0\u00a0\u00a0&lt;Customer pos=\"6\" id=\"SPLIR\"\"\"&gt;&lt;\/Customer&gt;\r\n\u00a0\u00a0\u00a0\u00a0&lt;Customer pos=\"7\" id=\"LONEP\"\"\"&gt;&lt;\/Customer&gt;\r\n\u00a0\u00a0\u00a0\u00a0&lt;Customer pos=\"8\" id=\"GROSR\"\"\"&gt;&lt;\/Customer&gt;\r\n\u00a0\u00a0\u00a0\u00a0&lt;\/ROOT&gt;'\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0  \u00a0EXEC sp_xml_preparedocument @XMLDoc OUTPUT, @doc ;\r\n\u00a0\u00a0\u00a0\u00a0SELECT pos, id\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0FROM OPENXML ( @XMLDoc , '\/ROOT\/Customer', 1 )\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0WITH ( pos INT, id VARCHAR(5) )\u00a0\u00a0;\r\n\u00a0\u00a0\u00a0\u00a0EXEC sp_xml_removedocument @XMLDoc ;\r\n\u00a0\r\n<\/pre>\n<p>One could generate the xml document from the csv variable with only the id values easily like:<\/p>\n<pre class=\"theme:ssms2012-simple-talk lang:tsql decode:true\">\u00a0\u00a0\u00a0\u00a0SELECT '\r\n\u00a0\u00a0\u00a0\u00a0&lt;ROOT&gt;\r\n\u00a0\u00a0\u00a0\u00a0&lt;Customer id=\"SPAN\"&gt;+\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0REPLACE( @p, ',', '\"&gt;&lt;\/Customer&gt;\r\n\u00a0\u00a0\u00a0\u00a0&lt;Customer id=\"SPAN\"&gt;) + '\"&gt;&lt;\/Customer&gt;\r\n\u00a0\u00a0\u00a0\u00a0&lt;\/ROOT&gt;'<\/pre>\n<h4 id=\"nodes\">The nodes() method<\/h4>\n<p>This is a feature that was introduced in SQL Server 2005 using the xml datatype. You can use the nodes() method to shred the data that can be mapped to a new row. The value() method can be applied as shown below to extract the individual elements:<\/p>\n<pre class=\"theme:ssms2012-simple-talk lang:tsql decode:true\">\u00a0\u00a0\u00a0\u00a0DECLARE @doc XML;\r\n\u00a0\u00a0\u00a0\u00a0SET @doc = '\r\n\u00a0\u00a0\u00a0\u00a0&lt;ROOT&gt;\r\n\u00a0\u00a0\u00a0 &lt;Customer pos=\"1\" id=\"ALFKI\"\"\"&gt;&lt;\/Customer&gt;\r\n\u00a0\u00a0\u00a0\u00a0&lt;Customer pos=\"2\" id=\"lt;\/Customer&gt;\r\n\u00a0\u00a0\u00a0\u00a0&lt;Customer pos=\"3\" id=\"PERIC\"\"\"&gt;&lt;\/Customer&gt;\r\n\u00a0\u00a0\u00a0\u00a0&lt;Customer pos=\"4\" id=\"HUNGC\"\"\"&gt;&lt;\/Customer&gt;\r\n\u00a0\u00a0\u00a0\u00a0&lt;Customer pos=\"5\" id=\"SAVEA\"\"\"&gt;&lt;\/Customer&gt;\r\n\u00a0\u00a0\u00a0\u00a0&lt;Customer pos=\"6\" id=\"SPLIR\"\"\"&gt;&lt;\/Customer&gt;\r\n\u00a0\u00a0\u00a0\u00a0&lt;Customer pos=\"7\" id=\"LONEP\"\"\"&gt;&lt;\/Customer&gt;\r\n\u00a0\u00a0\u00a0\u00a0&lt;Customer pos=\"8\" id=\"GROSR\"\"\"&gt;&lt;\/Customer&gt;\r\n\u00a0\u00a0\u00a0\u00a0&lt;\/ROOT&gt;'\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\r\n\u00a0\u00a0\u00a0\u00a0SELECT D.element.value('@id', 'VARCHAR(5)'),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 D.element.value('@pos', 'INT')\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0FROM @doc.nodes('\/ROOT\/Customer') AS D ( element )\r\n\u00a0<\/pre>\n<h4 id=\"CLR\">Using a CLR function<\/h4>\n<p>With the popularity of CLR functions in SQL 2005 and beyond, string parsing has become much more versatie. It is much easier to create a simple CLR function, deploy the assembly and then use it as a function within t-SQL code. To implement this function you will have to enable the database option CLR enabled.Here is a C# function example that you can create in Visual Studio under the database project : \u00a0<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">using System;\r\nusing System.Data;\r\nusing System.Data.SqlClient;\r\nusing System.Data.SqlTypes;\r\nusing Microsoft.SqlServer.Server;\r\nusing System.Collections;\r\n\u00a0\r\npublic class ParseList\r\n{\r\n\u00a0\u00a0\u00a0 [SqlFunction(FillRowMethodName = \"FillRow\")]\r\n\u00a0\r\n\u00a0\u00a0\u00a0 public static IEnumerable CLRParseString(SqlString csv)\r\n\u00a0\u00a0\u00a0 {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SqlString Delim_ = \",\";\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 return csv.Value.Split(Delim_.Value.ToCharArray(0, 1));\r\n\u00a0\u00a0\u00a0 }\r\n\u00a0\r\n\u00a0\u00a0\u00a0 public static void FillRow(object value, out string csv)\r\n\u00a0\u00a0\u00a0 {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0csv = (string)value;\r\n\u00a0\u00a0\u00a0 }\r\n}\r\n<\/pre>\n<p>Once you build this function and created the .dll you can create an assembly and a table valued function that can be accessed from within t-SQL<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">CREATE ASSEMBLY ParseList FROM 'C:\\ParseList.dll'\r\nWITH PERMISSION_SET = SAFE\r\nGO\r\nCREATE FUNCTION CLRParseString ( @str NVARCHAR(4000))\r\nRETURNS\r\nTABLE ( val NVARCHAR(4000) )\r\nAS EXTERNAL NAME ParseList.ParseList.CLRParseString; \r\nGO\r\n<\/pre>\n<p>The usage is pretty straight forward like:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT * FROM CLRParseString( @p )\r\n<\/pre>\n<p>You can find several comparable CLR functions in newsgroups and blogs with heavy discussions on the performance implications of using them. For some of the popular benchmarks I would recommend the article by Erland mentioned in the references section.<\/p>\n<h3 id=\"Loop\">Simple WHILE loop<\/h3>\n<p>Another popular method is to use a procedural <b>WHILE<\/b> loop. Very popular among programmers by virtue of its simplicity, this method is not particularly efficient for larger datasets. Here is an example:<\/p>\n<pre class=\"theme:ssms2012-simple-talk lang:tsql decode:true\">\u00a0\u00a0\u00a0\u00a0WHILE LEN( @param ) &gt; 0 BEGIN \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 IF CHARINDEX( ',', @param ) &gt; 0 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT @val = LEFT( @param, CHARINDEX( ',', @param )\u00a0\u00a0- 1 ) ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0@param = RIGHT( @param, LEN( @param ) - CHARINDEX( ',', @param ) ) \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ELSE \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT @val = @param, @param = SPACE(0)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 EXEC('INSERT tbl VALUES (' + @val + ')' ) \r\n\u00a0\u00a0\u00a0\u00a0END<\/pre>\n<p class=\"MsoNormal\"><b>Inserting parsed values into multiple columns<\/b><\/p>\n<p class=\"MsoNormal\">When the passed values are to be returned as column values in a single row rather than multiple rows, we can use the PIVOT function. Since we will always know the number of columns that will be in the resultset, formulating the query with a static PIVOT list will not be too difficult.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT R.*\r\n\u00a0 FROM ( SELECT SUBSTRING( ',' + @p + ',', n + 1, \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CHARINDEX( ',', ',' + @p + ',', n + 1 ) - n - 1 ) AS \"value\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ROW_NUMBER() OVER ( ORDER BY n ) AS \"pos\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0 FROM Nbrs\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0 WHERE SUBSTRING( ',' + @p + ',', n, 1 ) = ','\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0 AND LEN( ',' + @p + ',' ) &gt; n ) AS\u00a0 parsed\r\nPIVOT ( MAX(\"value\") FOR pos IN (\"1\", \"2\", \"3\", \"4\", \"5\", \"6\", \"7\", \"8\") ) R\r\n\u00a0\r\n<\/pre>\n<h2 id=\"Concl\">Conclusion<\/h2>\n<p>Be judicious and apply common sense while using any of the string parsing routines in t-SQL. Each of the methods described above has certain benefits in some scenarios and may not be appropriate for all situations. If you need additional detailed analysis of these and a few other methods including performance considerations, consider the articles in the references section.<\/p>\n<h2 id=\"Refer\">References<\/h2>\n<ul>\n<li><a href=\"http:\/\/www.sommarskog.se\/arrays-in-sql.html\">Arrays and Lists in SQL Server<\/a><\/li>\n<li><a href=\"http:\/\/www.simple-talk.com\/sql\/t-sql-programming\/tsql-string-array-workbench\/\">TSQL String Array Workbench<\/a><\/li>\n<li><a href=\"http:\/\/www.simple-talk.com\/sql\/t-sql-programming\/importing-text-based-data-workbench\/\">Importing Text-based data: Workbench<\/a><\/li>\n<\/ul>\n<h2 id=\"Acknow\">Acknowledgements<\/h2>\n<p>Aaron Bertrand, Erland Sommarskog, Umachandar Jayachandran, Linda Wierzbecki, Joe Celko, Alejandro Mesa, Kristen Hodges and Itzik Ben-Gan.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>It is a simple routine that we all need to use occasionally; parsing a delimited list of strings in TSQL. In a perfect relational world, it isn&#8217;t necessary, but real-world data often comes in a form that requires one of the surprising variety of routines that Anith Sen describes, along with sage advice about their use.&hellip;<\/p>\n","protected":false},"author":10401,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143531],"tags":[4150,4252,4190],"coauthors":[6794],"class_list":["post-427","post","type-post","status-publish","format-standard","hentry","category-t-sql-programming-sql-server","tag-sql","tag-t-sql-programming","tag-tsql"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/427","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/10401"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=427"}],"version-history":[{"count":10,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/427\/revisions"}],"predecessor-version":[{"id":74023,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/427\/revisions\/74023"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=427"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=427"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=427"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=427"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}