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

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