UDF Back Doors (fn_removepattern Undocumented UDF)

UDF Back Doors

Read this article in my SQL Server Mag... Wanted to remember it.

I want to play with the fn_removepattern UDF.

Here's a snip from the mag.
"...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"