I created Common Language Runtime (CLR) string functions that you can call from T-SQL,Visual Basic (VB),Visual C#, or Visual C++ code. Here's a list of the string functions and what they do:
- AT(): Returns the beginning numeric position of the nth occurrence of a string within an expression, counting from the leftmost character.
- RAT(): Returns the numeric position of the last (rightmost) occurrence of a string within an expression.
- OCCURS(): Returns the number of times a string occurs within an expression, including overlaps.
- OCCURS2(): Returns the number of times a string occurs within an expression, excluding overlaps.
- PADL(): Returns a string of a specified length (padded with spaces or characters if needed) from the left side of an expression.
- PADR(): Returns a string of a specified length (padded with spaces or characters if needed) from the right side of an expression.
- PADC(): Returns a string of a specified length (padded with spaces or characters if needed) from both sides of an expression.
- CHRTRAN(): Replaces each character in an expression that matches a character in a second expression with the corresponding character in a third expression.
- STRTRAN(): Searches an expression for occurrences of a second expression, then replaces each occurrence with a third expression. STRTRAN() is similar to the T-SQL REPLACE function, except STRTRAN() has three additional parameters to help refine searches.
- STRFILTER(): Removes all characters from a string except those specified.
- GETWORDCOUNT(): Counts the words in a string.
- GETWORDNUM(): Returns a specified word from a string.
- GETALLWORDS(): Inserts the words from a string into a table.
- PROPER(): Returns from an expression a string capitalized as appropriate for proper names.
- RCHARINDEX(): Returns the starting position of the specified string in an expression. Is similar to the T-SQL CHARINDEX function, except the search starts from the right instead of the left.
- ARABTOROMAN(): Returns the Roman numeral equivalent of a specified numeric expression (from 1 to 3999).
- ROMANTOARAB(): Returns the number equivalent of a specified Roman numeral expression (from I to MMMCMXCIX).
You'll find these 17 functions in the UDFs_Transact-SQL.zip file, which you can download from the SQL Server Magazine Web site or the Universalthread Web site (http://www.universalthread.com/wconnect/wc.dll?LevelExtreme~2,54,33,29527). The UDFs_Transact-SQL.zip file includes .chm files in English, French, Spanish, German, and Russian.
To create these functions in a SQL Server 2005 database, follow these steps:
- Open the appropriate T-SQL file (create_udfs_functions_VB.sql for VB, create_udfs_functions_CS.sql forVisual C#, or create_udfs_functions_CPP.sql for Visual C++).
- In theT-SQL file, set the path to appropriate DLL file (udfs_transact-sql_vb.dll for VB, udfs_transact-sql_cs.dll for Visual C#, or udfs_transact-sql_ cplusplus.dll for Visual C++).
- Execute the appropriate T-SQL file.
To delete the functions, open the drop_ udfs_functions_CLR.sql file and execute it.
Besides the versions for SQL Server 2005 T-SQL CLR, you'll find versions for SQL Server T-SQL, Sybase Adaptive Server Anywhere (ASA) T-SQL, DB2 Procedural Language/SQL(PL/SQL), and Oracle PL/SQL on the Universalthread Web site. More than 10,000 people have already downloaded the functions.