On Universalthread.com, I offer many T-SQL user-defined string functions that you can download for free. Versions are available for SQL Server 2005, SQL Server 2000, and SQL Server 7.0. Plus, versions are available for Oracle PL/SQL, Sybase Adaptive Server Anywhere (ASA), and DB2.
Here’s a list of the functions and what they do:
- 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 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, then replaces each occurrence with a third character expression. (Unlike the built-in replace T-SQL function, 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 a table.
- PROPER()—Returns from a character expression a string capitalized as appropriate for proper names.
- RCHARINDEX()—Is similar to the built-in charindex T-SQL function, but the search of which is on the right.
- ARABTOROMAN()—Returns the character Roman number equivalent of a specified numeric expression (from 1 to 3999).
- ROMANTOARAB()—Returns the number equivalent of a specified character Roman number expression (from I to MMMCMXCIX).
Note that my AT, PADL, PADR, CHRTRAN, and PROPER functions are similar to the Oracle PL/SQL INSTR, LPAD, RPAD, TRANSLATE, and INITCAP functions, respectively.
More than 7000 people have already downloaded the functions. I hope you will find them useful as well.
Editor’s Note
Share your SQL Server discoveries, comments, problems, solutions, and experiences with products and reach out to other SQL Server Magazine readers. Email your contributions (400 words or less) to [email protected]. Please include your phone number. We edit submissions for style, grammar, and length. If we print your submission, you’ll get $50.
