|
SQL Server T-SQL User-Defined Function of the WeekThe Undocumented system UDFs and an example fn_chariswhitespaceVolume 1 Number 22 April 15, 2003 Sign up for this newsletter at: http://www.novicksoftware.com/UdfOfWeek/UDFofWeekSignup.htm Check out the UDF Frequently Asked Questions at: http://www.novicksoftware.com/UdfOfWeek/UDF_FAQ.htm To ask a UDF related question or to contribute a UDF, send e-mail to udf@NovickSoftware.com Issue 14 introduced fn_get_sql a new system UDF that you get with Service Pack 3. It brings to 10 the number of system UDFs. If you take a peak at the list of functions in master you'll find that there are also many undocumented functions available in SQL Server 2000. This week's issue introduces the topic of the undocumented system UDFs. The first thing I want to show to you is the list of all the functions in master. Login as a system administrator to run this script: /-------- Start copying below this line ------------------------\ -- List all the system UDFs including the undocumented ones. use master go select CASE WHEN routine_schema = 'system_function_schema' THEN 'sfs' ELSE routine_schema END as [Owner] , routine_name , UDFofWeek.dbo.udf_SQL_DataTypeString(data_type , character_maximum_length , numeric_precision , Numeric_scale) as [Type] , Is_Deterministic as [Det] FROM information_schema.Routines WHERE ROUTINE_TYPE = 'FUNCTION' ORDER BY routine_schema, routine_name Go \------- Stop copying above this line -------------------------/ (Results) Owner routine_name Type Det ----- ---------------------------------------- -------------- --- dbo fn_isreplmergeagent bit NO dbo fn_MSFullText TABLE NO dbo fn_MSgensqescstr nvarchar(4000) NO dbo fn_MSsharedversion nvarchar(10) NO dbo fn_sqlvarbasetostr nvarchar(4000) NO dbo fn_varbintohexstr nvarchar(4000) NO dbo fn_varbintohexsubstring nvarchar(4000) NO sfs fn_chariswhitespace bit NO sfs fn_dblog TABLE NO sfs fn_escapecmdshellsymbols nvarchar(4000) NO sfs fn_escapecmdshellsymbolsremovequotes nvarchar(4000) NO sfs fn_generateparameterpattern nvarchar(806) NO sfs fn_get_sql TABLE NO sfs fn_getpersistedservernamecasevariation nvarchar(128) NO sfs fn_helpcollations TABLE NO sfs fn_hex varchar(8000) NO sfs fn_listextendedproperty TABLE NO sfs fn_myhelpcollations TABLE NO sfs fn_removeparameterwithargument nvarchar(3200) NO sfs fn_repladjustcolumnmap varbinary NO sfs fn_replbitstringtoint int NO sfs fn_replcomposepublicationsnapshotfolder nvarchar(255) NO sfs fn_replgenerateshorterfilenameprefix nvarchar(4000) NO sfs fn_replgetagentcommandlinefromjobid nvarchar(3200) NO sfs fn_replgetbinary8lodword int NO sfs fn_replinttobitstring char(32) NO sfs fn_replmakestringliteral nvarchar(4000) NO sfs fn_replprepadbinary8 binary NO sfs fn_replquotename nvarchar(4000) NO sfs fn_replrotr int NO sfs fn_repltrimleadingzerosinhexstr nvarchar(8) NO sfs fn_repluniquename nvarchar(100) NO sfs fn_serverid int NO sfs fn_servershareddrives TABLE NO sfs fn_shiftleft bigint NO sfs fn_shiftright bigint NO sfs fn_skipparameterargument nvarchar(3200) NO sfs fn_sql_filestatsbydrivetab TABLE NO sfs fn_testwhatcharsgoinaname_ int NO sfs fn_testwhatcharsgoinaname_1 int NO sfs fn_trace_geteventinfo TABLE NO sfs fn_trace_getfilterinfo TABLE NO sfs fn_trace_getinfo TABLE NO sfs fn_trace_gettable TABLE NO sfs fn_updateparameterwithargument nvarchar(3200) NO sfs fn_virtualfilestats TABLE NO sfs fn_virtualservernodes TABLE NO To make the listing fit in an e-mail I've shortened the schema name system_function_schema to sfs. I've also used the function udf_SQL_DataTypeString from issue #18 to give a convenient listing to the data type. I keep it in a database named UDFofWEEK. You'll have to change the reference to point to it where every you defined it. system_function_schema owns most of the UDFs in master, but not all. A few are owned by dbo. These aren't really system UDFs. They're defined in master. Because they don't reference any table data it doesn't matter who the owner is, they can be used from any database without worry. That brings us to the difference between an ordinary UDF and a system UDF. Like a system stored procedure, a true system UDF runs in the context of the database that it's invoked from rather than the database that is defined in. I'll show you more about this difference in future issues of this newsletter. Our UDF of the week is fn_chariswhitespace. This function is useful when trimming or word wrapping text. It accepts a string as input and responds with a BIT result of 1 when the character is a white space character and 0 when the character is not white space. The syntax of the call is: fn_chariswhitespace (@char) @char is an nchar(1) character. Notice that there's no need to precede the function name with the owner name. That's because it's a true system UDF. The white space characters are listed in this table: ASCII Values of White Space Characters Name Decimal Value Hex Value --------------- --------------- ----------------------- Tab 9 0x9 New Line 10 0xA Carriage Return 13 0xD Space 32 0x26 Here's a query that illustrates the results from fn_chariswhtespace: /-------- Start copying below this line ------------------------\ -- check if various characters are white space characters select fn_chariswhitespace(CHAR(9)) as [Tab] , fn_chariswhitespace('A') as [A] , fn_chariswhitespace(' ') as [Space] , fn_chariswhitespace(N' ') as [Unicode Space] , fn_chariswhitespace(NCHAR(10)) [Unicode New Line] , fn_chariswhitespace(N'A') [Unicode A] go \------- Stop copying above this line -------------------------/ (Results) Tab A Space Unicode Space Unicode New Line Unicode A ---- ---- ----- ------------- ---------------- --------- 1 0 1 1 1 0 This query could be run from master but also from any other database without changing the reference. Because the UDF is owned by system_function_schema, it can be called from anywhere without referring to the database where it's defined and without referring to the owner name. If you want to be a bit more systematic about which characters are white space, run this batch that shows the white space status of the first 255 possible characters: /-------- Start copying below this line ------------------------\ -- show all white space characters DECLARE @C int -- character in question SET @C=1 WHILE @C<256 BEGIN IF fn_chariswhitespace(CHAR(@C))=1 PRINT 'Whitespace Code: ' + convert(varchar, @c) + ' ' SET @C = @C + 1 END -- WHILE Go \------- Stop copying above this line -------------------------/ (Results) Whitespace Code: 9 Whitespace Code: 10 Whitespace Code: 13 Whitespace Code: 32 I've checked and none of the Unicode values over 255 are white space. Of course, all of the above information could also be gleaned by examining the listing of the function. You can do that with udf_SQL_SystemUDFText which was in issue #17, which you can find from here: http://www.NovickSoftware.com/UDFofWeek/UDFofWeekArchives.htm Please share this issue with anyone interested in SQL Server. Thanks. +--------------------------------------------------------------+ Do you have a great UDF that you'd like to share? Or maybe you have a T-SQL problem that you think could be solved by a UDF but you don't know how? Send them to: UDF@NovickSoftware.com and they might be published in this newsletter. I try and respond to every request that I get. Thanks, Andrew Novick +--------------------------------------------------------------+
|
|
