User-Defined string functions Transact-SQL

Functions:AT(): Returns the beginning numeric position of the nth occurrence of a character expression within another character expression, counting from the leftmost character (including overlaps). The search performed by AT() is case-sensitive. AT similar to the Oracle function INSTR.RAT(): Returns the numeric position of the last (rightmost) occurrence of a character string within another character string (including overlaps). The search performed by RAT() is case-sensitive.ATC(): Returns the beginning numeric position of the first occurrence of a character expression within another character expression, counting from the leftmost character (including overlaps). The search performed by ATC() is case-insensitive. ATC similar to the Oracle function INSTR.RATC(): Returns the numeric position of the last (rightmost) occurrence of a character string within another character string (including overlaps). The search performed by RATC() is case-insensitive.

AT2(): Returns the beginning numeric position of the first occurrence of a character expression within another character expression, counting from the leftmost character (excluding overlaps). The search performed by AT2() is case-sensitive. AT2 similar to the Oracle function INSTR.

ATC2(): Returns the beginning numeric position of the first occurrence of a character expression within another character expression, counting from the leftmost character (excluding overlaps). The search performed by ATC2() is case-insensitive. ATC similar to the Oracle function INSTR.

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). OCCURS2 is faster than OCCURS.

PADL(): Returns a string from an expression, padded with spaces or characters to a specified length on the left side. PADL similar to the Oracle function LPAD.

PADR(): Returns a string from an expression, padded with spaces or characters to a specified length on the right side. PADR similar to the Oracle function RPAD.

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. CHRTRAN similar to the Oracle function TRANSLATE.

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. PROPER similar to the Oracle function INITCAP.

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).

Thanks to Igor Nikiforov

Download code at: