Skip navigation

Handy CLR Functions That You Can Call from T-SQL Code

Download the Code iconI 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 Download the Code icon at the top of the page. 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:

  1. 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++).
  2. 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++).
  3. 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.

Hide comments

Comments

  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.
Publish