Creating a User Defined Function in SQL for Comma-Delimited Searches

Introduction
In a previous article, Using Sets with SQL Server, author Scott Mitchell looks at how to use the IN operator in a SQL WHERE clause. The IN operator can be used to filter records that match a specified comma-delimited list, or the results of another SQL query. For example:

Select employeeId, -- integer (int) FirstName, LastName from Employees Where employeeId IN (1,3,6,7,13)

Will return employees from the Employees table where the employeId field equals 1, 3, 6, 7, or 13. Similarly, we could just get employees Bob, Mary, and John using the following query:

Select employeeId, FirstName, -- character data (nvarchar) LastName from Employees Where FirstName IN ('Bob','Mary','John')

The IN operator also can be used with a separate SQL SELECT statement. For example, if we had a separate table firedEmployees, which had a record for each fired employee, along with information about their termination (when they were fired, why they were let go, etc.). If we wanted to obtain employee information about those who had been fired, we could use:

Select * from Employees Where employeeId IN (Select employeeId From firedEmployees)

Armed with this knowledge about the IN operator, you might want to create a stored procedure that takes in a comma-delimited string and retrieves those records that match the string. Something like:

CREATE PROCEDURE getEmployeesByFirstName ( @CommaDelimitedListOfFirstNames nvarchar(1000) ) AS Select * from Employees Where FirstName IN (@CommaDelimitedListOfFirstNames)

However, the IN operator cannot accept a comma-delimited string. It only accepts comma-delimited constants or the results of a SQL query. There are two workarounds that I'm familiar with to overcome this hurdle. One is to create an nvarchar local variable in the stored procedure, and concatenate the SQL query in with the comma-delimited list to use with the IN operator. An example of this is discussed in this SQL Guru post. While this option is viable, it leads to rather unreadable stored procedure syntax that can be difficult to debug.

A cleaner approach is to create a user-defined function (UDF) that can take a comma-delimited string and convert it into the table format the IN operator can work with. In this article we'll examine how to implement this latter approach.

Creating a Split UDF
What we need to do is to create a function that takes a comma-delimited string and splits it into a recordset representation. Since this functionality is similar to the Split function in various programming languages, which splits a delimited string into a string array, I decided to call this UDF Split.

There are generally two parameters to a split function: the list to split and the charatcer(s) to split on, the delimiter. In the following function we begin by declaring our input variables - @List, the list to split, and @SplitOn, the delimiter(s) to split on. The return value of this UDF is a table with two fields: Id, an identity column, and Value, an nvarchar(100) column.

What Split will do is turn a delimited list into a table with a row for each delimited "piece" of the input string. So, if you passed in the string "1,3,6,7,10" and specified the delimiter as ",", you'd get back a five-record table with the row values: "1", "3", "6", "7", and "10".

Now, to create a user-defined function for Microsoft SQL Server, use Enterprise Manager and expand the database for which you want to add a UDF. You should see a 'User Defined Functions' label expanded underneath the database. Right-click and select 'New User Defined Function' (see the screenshot below). From here, enter the code for the UDF. (Note: we'll be examining the entire UDF in pieces over the remainder of this article - you need to add the entire code to the UDF. You can view the complete UDF code here. For more information on UDFs, be sure to read: User Defined Functions.)

CREATE FUNCTION dbo.Split ( @List nvarchar(2000), @SplitOn nvarchar(5) ) RETURNS @RtnValue table ( Id int identity(1,1), Value nvarchar(100) ) AS BEGIN

The main body of the function simply loops through the string finding the first occurrence of the delimiter on each pass. Once the delimiter has been found, the string is broken into two pieces. The first piece is inserted into the result table while the second piece replaces the original list. The loop continues until no more occurrences of the delimiter are found. Lastly, the remainder of the list is added to the result table.

To start the loop, we want our while condition to check for the existence of the delimiter in our list string. The T-SQL function Charindex will return the character position of the first occurrence of the delimiter. To use the Charindex function we pass first the delimiter (@SplitOn) and second the list string (@List). (For more information on CharIndex be sure to consult the technical documentation.)

While (Charindex(@SplitOn,@List)>0) Begin

Once we are inside the loop, we know that the delimiter still exists in the string. Using the Substring function, which requires the 'source string', the 'starting character number', and the 'number of characters', we can retrieve all of the characters before the delimiter. The 'number of characters' is determined by using the Charindex function. We use the same parameters we used in the while condition and are returned the position of the first character of the delimiter. Since we do not want to include any part of the delimiter in our value we subtract one from the position to get the length. (For more information on the Substring function, consult the technical docs.)

Insert Into @RtnValue (value) Select     Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1)))

Now that we have stored the first value in the list, we want to remove the first value and the delimiter that follows it. We will again use the Substring function. This time the 'starting character number' is found by adding the first position to the length of the delimiter. The 'number of characters' is the length of the list, which will exceed the length of the string (In SQL this is not a problem for Substring, but not a good practice across the board). The End continues the loop.

Set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List)) End

Finally, after finishing the loop there may be a value left in the list, so we store what ever is left just in case. Return the table and you have a split function.

Insert Into @RtnValue (Value)     Select Value = ltrim(rtrim(@List))     Return END

Using the Split UDF
Now that we've created the Split user-defined function, let's look at using it in a SQL query or stored procedure. First, before seeing an example, realize that the Split UDF returns a recordset with the Value as type nvarchar. Depending on the data type of the column that's participating in the IN operator, you might need to use the T-SQL CONVERT method to cast Value to that data type. For example, in the following syntax you can see how Value must be cast to an int because employeeId is an int.

CREATE PROCEDURE getEmployeesByID(@employeeList nvarchar(1000)) AS Select employeeId, -- integer (int) FirstName, LastName from #myTable Where employeeId IN (Select convert(int,Value) from dbo.Split(@employeeList,','))

Notice how this function is used - Split returns a recordset, and we pick out the Value portion of this recordset using a SELECT statement where the table name is Split. Split receives two input parameters - the delimited list (@employeeList, in this example), and the delimiter.

An ASP/HTML Example
One of the projects I have worked on was a user management system that maintained the access rights to a several internal Web applications as well as various permissions in each application. I stored the access rights in a table called hasAccess that contained the columns userId and permissionId. To manage a user's access, I used an ASP page with two multiple <select> lists.

One list provides a list of permissions the user doesn't have, the other a list of what permissions they do have. The user interface, then, allows the user to move permissions back and forth between this two disparate lists. The HTML markup sent to the browser for a user with no access to permissions #1, #5, #7, and #8, but access for permissions #2, #3, #4, and #6, would look like:

<select name="noAccessList"> <option value="1">Permission #1</option> <option value="5">Permission #5</option> <option value="7">Permission #7</option> <option value="8">Permission #8/option> </select> ... <select name="hasAccessList"> <option value="2">Permission #2</option> <option value="3">Permission #3</option> <option value="4">Permission #4/option> <option value="6">Permission #6/option> </select>

With such permissions there would be four rows in the hasAccess table for this user:

userId		permissionId
-----------	------------
1                     2
1                     3
1                     4
1                     6

If I want to delete multiple permissions from the hasAccess table - say permissions 2, 3, and 6 - then I will select those three permissions in the list box and then submit my form. On the other side of the post, Request.Form("hasAccess") will return the comma separate list "2,3,6". I can then pass this string to a stored procedure and have it use the Split UDF to remove those permissions.

CREATE PROCEDURE DeletePermissions(@UserId int, @removeAccessList nvarchar(255)) AS Delete From [hasAccess] Where userId = @UserId and permissionId in (Select convert(int,Value) From dbo.Split(@removeAccessList,','))

Happy Programming!