Creating a User Defined Function in SQL for Comma-Delimited SearchesBy Corey Aldebol
In a previous article, Using Sets with SQL Server, author Scott Mitchell looks at how to use the IN operator in a SQL
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:
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:
The IN operator also can be used with a separate SQL
SELECT statement. For example, if we had a separate
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:
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:
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
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.
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
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
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.)
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
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
@List). (For more information on
CharIndex be sure to consult the
Once we are inside the loop, we know that the delimiter still exists in the string. Using the
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
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.
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.
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
employeeId is an
Notice how this function is used -
Split returns a recordset, and we pick out the
portion of this recordset using a
SELECT statement where the table name is
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
permissionId. To manage a
user's access, I used an ASP page with two multiple
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:
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.