|
Igor2004
More clever than you
78 Posts |
 Posted - 2006-02-03 : 11:24:17
|
| User-Defined string Functions MS SQL Server 2005 Transact-SQL SQLCLR (VB. Net, C#.Net, C++. Net)Ladies and Gentlemen,I am pleased to offer, free of charge, the following string functions MS SQL Server 2005 Transact-SQL SQLCLR (VB. Net, C#.Net, C++. Net) with source code:AT(): Returns the beginning numeric position of the nth occurrence of a character expression within another character expression, counting from the leftmost character.RAT(): Returns the numeric position of the last (rightmost) occurrence of a character string within another character string. OCCURS(): Returns the number of times a character expression occurs within another character expression (including overlaps).OCCURS2(): Returns the number of times a character expression occurs within another character expression (excluding overlaps). PADL(): Returns a string from an expression, padded with spaces or characters to a specified length on the left side. PADR(): Returns a string from an expression, padded with spaces or characters to a specified length on the right side. PADC(): Returns a string from an expression, padded with spaces or characters to a specified length on the both sides. CHRTRAN(): Replaces each character in a character expression that matches a character in a second character expression with the corresponding character in a third character expression. STRTRAN(): Searches a character expression for occurrences of a second character expression, and then replaces each occurrence with a third character expression. Unlike a built-in function Replace, STRTRAN has three additional parameters.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 the table.PROPER(): Returns from a character expression a string capitalized as appropriate for proper names. RCHARINDEX(): Similar to the Transact-SQL function Charindex, with a Right search.ARABTOROMAN(): Returns the character Roman numeral equivalent of a specified numeric expression (from 1 to 3999).ROMANTOARAB(): Returns the number equivalent of a specified character Roman numeral expression (from I to MMMCMXCIX).AT, PADL, PADR, CHRTRAN, PROPER: Similar to the Oracle functions PL/SQL INSTR, LPAD, RPAD, TRANSLATE, INITCAP.Plus, there are CHM files in English, French, Spanish, German and Russian.Plus, there are versions for MS SQL SERVER, SYBASE ASA, DB2, Oracle.More than 8000 people have already downloaded my functions. I hope you will find them useful as well.For more information about string UDFs MS SQL Server 2005 Transact-SQL SQLCLR (VB. Net, C#.Net, C++. Net) please visit thehttp://www.universalthread.com/wconnect/wc.dll?LevelExtreme~2,54,33,29527Please, download the filehttp://www.universalthread.com/wconnect/wc.dll?LevelExtreme~2,2,29527The accuracy criteria of user-defined functionsGETWORDNUM() , GETWORDCOUNT(), GETALLWORDS(), GETALLWORDS2() working with strings:1) Both character parameters are empty - the function returns nothing - either 0 strings, or 0.2) The first parameter is not empty, the second is empty - the function returns the table from one string or 1. The first parameter is in this case the required word.3) The function works correctly with strings starting and/or finishing with one or several delimiters.4) The function works correctly on strings consisting only of delimiters - a result in this case is 0 strings, or 0 words.5) The function works correctly irrespective of the character sets in the string or delimiters, including /, \,?, ^, %, -, ' etc., that is, any character that have special function. No exceptions, if the parameters of an incorrect type are transmitted, there is a standard error message.Above mentioned and other functions completely correspond to these criteria. With the best regards,Igor Nikiforov |
|
User-Defined string Functions SQLCLR