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.