Creating a User Defined Function in SQL for Comma-Delimited Searches
By Corey Aldebol
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:
|
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
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:
|
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 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.)

|
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)
|
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)
|
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))
|
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.
|
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
.
|
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:
|
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.
|
Happy Programming!