SQL Server Magazine

The SQL Server installation program runs regular .sql script files to create the special UDFs, so you can create them, too. The characteristics of special UDFs are that

• they're created in the master database
• they have the owner system_function_schema
• their names begin with fn_
• their names are all lowercase, even in a case-insensitive environment

However, if you try creating a function that has all these characteristics, you'll get an error message saying that the owner system_function_schema doesn't exist or that you don't have permissions to create an object with that owner. The system_function_schema user does exist in the master database's sysusers table, but you're not usually allowed to use it. Investigating the installation script files produces the missing key—you need to turn on the allow updates server configuration option to get permission to create your special UDFs, specifying the system_function_schema user as the owner. But be sure to turn the allow updates option off immediately afterward so that SQL Server won't accept direct changes to system tables.

Run the script that Listing 1, page 23, shows to create the special scalar UDF fn_removepattern(), which you can use to remove all occurrences of a pattern from a given character string. The function accepts three arguments: @str is the input character string, @pattern is the pattern you want to locate, and @deletelength is the number of characters you want to delete from the positions the pattern is in. You need to format the @pattern argument the same way you format patterns for the LIKE predicate or the PAT- INDEX() function. (For details, see SQL Server Books Online—BOL—under LIKE.)

The reason for requesting the @deletelength parameter and not having the function's code calculate the length of the searched pattern is that the pattern length doesn't necessarily match the length of the substrings that you want to remove. For example, you might want to locate all occurrences of the pattern '%bc%' and remove the two characters bc. In that case, you'd specify 2 in the @deletelength argument. The fn_removepattern() function uses the PATINDEX() function to locate the pattern within the input character string, then uses the STUFF() function to remove characters from it repeatedly until it doesn't find the pattern anymore. To test the fn_removepattern() function, run the following code from any database:

SELECT fn_removepattern
('abcabcabcabcabc', '%bc%', 2)

You get 'aaaaa' as a result.

To use table columns as arguments for the function, run the following query:

SELECT CustomerID, Address,
fn_removepattern(Address, '%[^0-9]%', 1) AS AddrNumsOnly
FROM Northwind.dbo.Customers

Examine the abridged results that Figure 1 shows and notice in the result column AddrNumsOnly that the function removed all the non-numeric characters from the Address column.

As an example of creating a table-valued special UDF, run the code that Listing 2 shows to create the fn_nums() function, which returns a table containing the requested number of consecutive integers, starting with 1. The function first inserts the value 1 into the table variable @Nums. It then enters a loop and uses an INSERT SELECT statement in each iteration. The statement takes the existing rows from @Nums, adds a factor equal to the current number of rows in @Nums to the column n, and inserts the result back into @Nums. This loop multiplies the number of rows in @Nums in each iteration, stopping when the value of column n reaches the requested number of integers. The INSERT loop is efficient because it involves very few INSERT statements. To test the fn_nums() function, run the following query, which requests a table containing the numbers 1 through 10:

SELECT * FROM ::fn_nums(10)

This query returns the results that Figure 2 shows.

Prev. page     1 [2] 3     next page