SQL User-Defined Function: ListToTable

by Simon. Average Reading Time: about 2 minutes.

A common need in SQL is the ability to iterate over a list as if it were an array. In SQL it is not possible to declare arrays, unlike other programming languages such as ColdFusion, ActionScript and Java. Fortunately, there is a way around this problem: use a User-Defined Functions (UDFs) to create a tabular version of the data. Arrays are, after all, essentially tabular data (at their simplest, one dimension level).

A User-Defined Function, is a function provided by the user of a program or environment. In SQL databases, a user-defined function provides a mechanism for extending the functionality of the database server by adding a function that can be evaluated in SQL statements.

The Function Code

Below is the complete function definition:

<|/1/>CREATE <|/1/>FUNCTION dbo.udf_ListToTable
	@LIST 		<|/2/>NVARCHAR(<| style="color: #cc66cc;">4000),
	@DELIMITER 	<|/2/>NVARCHAR(<| style="color: #cc66cc;">10) = ','
<|/1/>RETURNS @ListTable <|/1/>TABLE
	Item <|/2/>NVARCHAR(<| style="color: #cc66cc;">200)
	<|/1/>DECLARE @LenDel 	<|/2/>INT
	<|/1/>DECLARE @Pos 		<|/2/>INT
	<|/1/>DECLARE @Item 		<|/2/>NVARCHAR(<| style="color: #cc66cc;">200)
	--Get the length of the delimiter, use hack to get around LEN(' ') = 0 issue
	<|/1/>SET @LenDel = LEN(@DELIMITER + '|') - <| style="color: #cc66cc;">1
	<|/1/>WHILE @Pos > <| style="color: #cc66cc;">0
		--Get the item
		<|/1/>SET @Item = SUBSTRING(@LIST, <| style="color: #cc66cc;">1, @Pos-<| style="color: #cc66cc;">1)
		--Add it to the table (if not empty string)
		<|/1/>IF LEN(LTRIM(@Item)) > <| style="color: #cc66cc;">0
			<|/1/>INSERT @ListTable (Item) <|/1/>VALUES (LTRIM(@Item))
		--Remove the item from the list
		<|/1/>SET @LIST = STUFF(@LIST, <| style="color: #cc66cc;">1, @Pos+@LenDel-<| style="color: #cc66cc;">1, '')
		--Get the position of the next delimiter
	--Add the last item to the table (if not empty string)
	<|/1/>IF LEN(LTRIM(@LIST)) > <| style="color: #cc66cc;">0
		<|/1/>INSERT @ListTable (Item) <|/1/>VALUES (LTRIM(@LIST))

The function simply loops over the list passed into the function. Each list item is then inserted into the variable named @ListTable, which is of type TABLE. The @ListTable variable is then returned out of the function and can be handled the same as any other table.

The Function In Use

A simple demonstration is as follows:

<|/1/>INSERT <|/1/>INTO tableName (column1, column2, column3, column4)
<|/1/>SELECT @variable1, @variable2, myTable.item, GETDATE()
<|/1/>FROM dbo.udf_ListToTable(@list,',') <|/1/>AS myTable

In this example, we insert the same information (@variable1, @variable2) for every instance of an item found in myTable.

This is useful, for example, if you want to apply a setting to a group of users. The group of users could be contained in a list that needs to be parsed as a table, whilst the individual setting details are contained in the other variables.

Download the Code

Download the code, rename the file to .sql and run on your database instance. You will then be able to reference the function in your Stored Procedures.

This article has been tagged

, , , , , , , , , , , , , ,

Other articles I recommend

SQL User-Defined Function: ReplaceChars

The SQL Replace function enables us to look for a certain character phrase in a string and replace it with another character phrase. The updated string is then returned by the function.

What is a SQL Injection Attack

Over the past few weeks, subversive elements in the international arena have decided that attacking websites is a fun thing to do! The online world has become the new battle ground between nations vying to de-stabilise rivals. This may seem all very Jack Bauer, but we are increasingly seening ‘SQL injection attacks’ eminating from countries such as Russia, China and North Korea. Of course, that doesn’t mean our countries aren’t doing the same in return, but we only see the results from foreign-based attacks.

A Quick Way to Help Optimise MySQL Databases

The ANALYSE() procedure built into MySQL can give you a wealth of information about your database tables, making it easy to find out which tables need optimising.