Burton Roberts' SQL Server Magazine article "Treat Yourself to Fn_Split()" (July 2001) reminded me of two user-defined functions (UDFs) I use regularly. Roberts' fn_Split() UDF emulates the Visual Basic (VB) Split() function and lets developers incorporate variable-length parameter arrays into their T-SQL programming. The first of my UDFs, ReturnVarcharTableFromList(), is a simpler version of the fn_Split() function. This UDF, which Listing 1 shows, returns a one-column table of values by parsing a string into individual words. The following command returns a result set containing four rows:
SELECT keyword
FROM dbo.fn_ReturnVarcharTableFromList
('This Is A List', 1)
This UDF isn't remarkable, but it leads me to the second UDF that I use: CamelCaseToSpace(). This UDF, which Listing 2 shows, parses input into individual words. Issuing the following command returns one column that has the value Firstname Lastname.
SELECT dbo.fn_CamelCaseToSpace
('FirstnameLastname')
Data transformations are often difficult and nearly always involve special parsing and translation logic. UDFs are perfect for solving such challenges. Using multiple functions in the same command demonstrates some real possibilities of UDFs. For example, the following command returns the result set that Figure 1 shows:
SELECT dbo.fn_CamelCaseToSpace(keyword)
FROM dbo.fn_ReturnVarcharTableFromList
('SQLServer2000EnterpriseCosts$20000Per
CPU DotNETStuff FirstnameLastname', 1)
You might also use these functions to avoid dynamic SQL inside stored procedures. Imagine a stored procedure that accepts a single parameter that contains a list of values to search for. SQL Server doesn't support the syntax WHERE column IN @parameter but does support the syntax WHERE column IN (select keyword from dbo.fn_ReturnVarcharTableFromList(@parameter, 1)).
