Espace Web anglais d'Igor NikiforovDate de publication : 09/08/2005
Par
Igor2004 (nikiforov.developpez.com)
User-defined string functions Transact-SQL, PL/SQL
MS SQL Server 6.5, 7.0, 2000, 2005 please, download the zip file
MS SQL Server 2005 SQLCLR (VB. Net, C#.Net, C++. Net) please, download the zip file
Oracle PL/SQL please, download the zip file
SQL Procedural Language DB2 please, download the zip file
Sybase ASA please, download the zip file
Ladies and Gentlemen,
I am pleased to offer, free of charge, the following string functions Transact-SQL:
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 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 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.
More than 15000 people have already downloaded my functions. I hope you will find them useful as well.
|
|