Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Downloads
Add
Search: 
Search

User-Defined string functions Transact-SQL
Igor Nikiforov, Belron Canada inc.
User-Defined string functions Transact-SQL AT, RAT, OCCURS, PADC, PADR, PADL, CHRTRAN, STRFILTER, STRTRAN, WORDTRAN, GETWORDCOUNT, GETWORDNUM, GETNUMWORD, GETALLWORDS, PROPER, RCHARINDEX, ARABTOROMAN, ROMANTOARAB etc. In my humble opinion, it will be convenient to apply such functions for writing of stored procedures and User-Defined Functions on Transact-SQL. AT, PADL, PADR, CHRTRAN, PROPER similar to the Oracle functions INSTR, LPAD, RPAD, TRANSLATE, INITCAP. Plus, there are CHM files in English, French, Spanish, German and Russian.
Created on
19 years ago
Downloads
17535
File type
Freeware
Rating
5.00/5.00
General information
URL:
UDFs_Transact-SQL.zip
Rating:
5.00/5.00 (6 rates) Rate this item
Description
Keywords: User-Defined string functions Transact-SQL Transact SQL UDFs UDF SQL Server 7.0 2000 2005 Yukon function T-SQL similar function Oracle PL/SQL DB2 Sybase ASA INSTR LPAD RPAD RPAD TRANSLATE INITCAP VFP Visual FoxPro Fox St_Denis AT RAT OCCURS PADC PADR PADL CHRTRAN STRFILTER STRTRAN WORDTRAN GETWORDCOUNT GETWORDNUM GETNUMWORD GETALLWORDS GETOCCURSWORD GETATWORD GETRATWORD PROPER RCHARINDEX ARABTOROMAN ROMANTOARAB Enterprise Edition 64 bit dynamic SQL statements with output parameters text ntext Sybase ASA occurrence appearance location whereabouts ubiety split cleave rend sever break up divide split up cut hack fracture pad padding expand enlarge amplify augment increase stuff word parol saying byword string sequence translation transformation conversion change alteration alter recast remodel make over search exploration examination inquiry scrutiny research renovation renewal rejuvenation capitalize capitalize letters capitalized slice chop example create Visual Extend Framework VFX dFPUG ----------------------------------------------------------------------------------- User-Defined string functions Transact-SQL: AT, RAT, OCCURS, PADC, PADR, PADL, CHRTRAN, STRTRAN, STRFILTER, GETWORDCOUNT, GETWORDNUM, GETNUMWORD, GETALLWORDS, PROPER, RCHARINDEX, ARABTOROMAN, ROMANTOARAB. In my humble opinion, it will be convenient to apply such functions for writing of stored procedures and User-Defined Functions on Transact-SQL MS SQL Server 7.0 (stored procedures), 2000, 2005. AT,( ATC, AT2,) PADL, PADR, CHRTRAN, PROPER similar to the Oracle functions INSTR, LPAD, RPAD, TRANSLATE, INITCAP The above mentioned functions work similarly as case sensitive functions irrespective of installations of Collation Settings in your database, i.e. the symbols 'A' and 'a' differ. ----------------------------------------------------------------------------------- To create those type of functions in the SQL database, start SQL Query Analyzer, open the file create_udfs_functions.sql, and execute it by pressing F5. To delete the given function, start SQL Query Analyzer, open the file drop_udfs_functions.sql, and execute it by pressing F5. _______________________________________________ If you use MS SQL Server 7.0 or earlier versions of MS SQL Server, which does not support the user-defined functions you can use the Stored Procedures. The Stored Procedures names and functionality corresponds to the names and functionality of UDFs, except the prefix SP_. To create those types of Stored Procedures in the SQL database, start SQL Query Analyzer, open the file create_procedure_sql_70_and_earlier.sql, and execute it by pressing F5. The description and the examples of the utilization of Stored Procedures are in the file create_procedure_sql_70_and_earlier.sql. To delete the given stored procedure, start SQL Query Analyzer, open the file drop_procedure_sql_70_and_earlier.sql, and execute it by pressing F5. ----------------------------------------------------------------------------------- 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. STRFILTER(): Removes all characters from a string except those specified. 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. WORDTRAN(): Searches a character string for occurrences of a first word, and then replaces each occurrence with a second word. GETALLWORDS(): Inserts the words from a string into the table. GETWORDCOUNT(): Counts the words in a string. GETWORDNUM(): Returns a specified word from a string. GETNUMWORD(): Returns the index position of a word in a string. GETOCCURSWORD(): Returns the number of times a word occurs within the character string. GETATWORD(): Returns the beginning numeric position of the nth occurrence of a word within the character string, counting from the leftmost character. GETRATWORD(): Returns the numeric position of the last (rightmost) occurrence of a word within the character string. 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). ----------------------------------------------------------------------------------- Je voudrais bien vous proposer gratuitement les fonctions en Transact-SQL qui sont les suivantes AT(): Renvoie la position numérique du début de la énième occurrence d'une expression caractère à l'intérieur d'une autre expression caractère, en comptant à partir du caractère le plus à gauche. RAT(): Renvoie la position numérique de la dernière occurrence (extrême droite) d'une chaîne de caractères au sein d'une autre chaîne de caractères. OCCURS(): Renvoie le nombre d'occurrences d'une expression caractère à l'intérieur d'une autre expression caractère (y compris les chevauchements éventuels). OCCURS2():Renvoie le nombre d'occurrences d'une expression caractère à l'intérieur d'une autre expression caractère (en excluant les chevauchements éventuels). PADL(): Renvoie une chaîne de caractères à la longueur spécifiée à partir d'une expression complétée à gauche par des espaces ou des caractères. PADR(): Renvoie une chaîne de caractères à la longueur spécifiée à partir d'une expression complétée à droite par des espaces ou des caractères. PADC(): Renvoie une chaîne de caractères à la longueur spécifiée à partir d'une expression complétée des deux côtés par des espaces ou des caractères. CHRTRAN(): Remplace chaque caractère dans une expression caractère, qui correspond à un caractère d'une deuxième expression caractère, par le caractère correspondant d'une troisième expression caractère. STRFILTER(): Enlève tous les caractères d'une chaîne sauf ceux qui sont spécifiés. STRTRAN(): Recherche dans une expression caractère des occurrences d'une deuxième expression caractère, puis remplace chaque occurrence par une troisième expression caractère. À la différence d'une fonction intégrée Replace, STRTRAN a trois paramètres supplémentaires. WORDTRAN(): Recherche dans une expression caractère des occurrences d’un premier mot, puis remplace chaque occurrence par un deuxième mot. GETALLWORDS(): Insère tous les mots d'une chaîne dans la table. GETWORDCOUNT(): Renvoie le nombre de mots à l'intérieur d'une chaîne. GETWORDNUM(): Renvoie un mot spécifié d'une chaîne. GETNUMWORD(): Renvoie la position d’un mot dans une chaîne de caractères. GETOCCURSWORD(): Renvoie le nombre d'occurrences d'un mot à l'intérieur d'une chaîne de caractère. GETATWORD(): Renvoie la position numérique du début de la énième occurrence du mot dans une chaîne de caractères. GETRATWORD() : Renvoie la position numérique du début de la énième occurrence, allant de droite à gauche, du mot dans une chaîne de caractères. PROPER(): Renvoie, à partir d'une expression caractère, une chaîne orthographiée comme un nom propre, c'est-à-dire avec une majuscule initiale. RCHARINDEX(): Est similaire à une fonction intégrée Charindex, mais la fonction commence la recherche à partir de la droite. ARABTOROMAN(): Renvoie un nombre romain équivalent à une expression numérique spécifiée. ROMANTOARAB(): Renvoie une expression numérique équivalente à un nombre romain spécifié. Pour plus d'information en langue française, veuillez visiter: http://nikiforov.developpez.com/ ----------------------------------------------------------------------------------- Quisiera poner a su alcance en forma gratuita las siguientes funciones de Transact-SQL: AT(): Devuelve la posición numérica inicial de la enésima aparición de una expresión de carácteres en otra expresión de carácteres; se cuenta desde el carácter situado más a la izquierda. RAT(): Devuelve la posición numérica de la enésima aparición (más a la derecha) de una cadena de carácteres dentro de otra cadena de carácteres. OCCURS(): Devuelve el número de veces que ocurre una expresión dentro de otra expresión de carácteres (incluye solapamientos). OCCURS2(): Devuelve el número de veces que ocurre una expresión dentro de otra expresión de carácteres (excluye solapamientos). PADL(): Devuelve una cadena a partir de una expresión, rellenándola desde la izquierda hasta la longitud especificada. PADR(): Devuelve una cadena a partir de una expresión, rellenándola desde la derecha hasta la longitud especificada. PADC(): Devuelve una cadena a partir de una expresión, rellenándola por ambos lados hasta la longitud especificada. CHRTRAN(): Cada carácter de una expresión de carácteres que coincida con un carácter de una segunda expresión de carácteres se reemplaza con el carácter correspondiente de una tercera expresión de carácteres. STRFILTER(): Quita todos los carácteres de una cadena de carácteres excepto aquellos especificados. STRTRAN(): Busca en una expresión de caracteres las apariciones de una segunda expresión de caracteres, y luego reemplaza cada aparición con una tercera expresión de caracteres (a distinción de la función incorporada replace, STRTRAN tiene tres parámetros adicionales). WORDTRAN(): Busca en una cadena de caracteres las apariciones de una primera palabra, y luego reemplaza cada aparición con una segunda palabra. GETALLWORDS(): Inserta las palabras de una cadena en la tabla. GETWORDCOUNT():Cuenta el número de palabras de una cadena. GETWORDNUM(): Devuelve la palabra especificada de una cadena. GETNUMWORD(): Devuelve la posición de una palabra en una cadena de caracteres. GETOCCURSWORD(): Devuelve el número de veces que aparece una palabra dentro de una cadena de caracteres. GETATWORD(): Devuelve la posición numérica inicial de la enésima aparición de una palabra en una cadena de caracteres contando de izquierda a derecha. GETRATWORD(): Devuelve la posición numérica de la última aparición (más a la derecha) de una palabra dentro de una cadena de caracteres. PROPER(): Devuelve, a partir de una expresión de tipo Carácter, una cadena con el modelo apropiado de mayúsculas/minúsculas para nombres propios. RCHARINDEX(): Es similar a la función incorporada Transact-SQL charindex, pero la función comienza la búsqueda por la derecha. ARABTOROMAN(): Devuelve el número romano equivalente de una expresión numérica especificada. ROMANTOARAB(): Devuelve la expresión numérica equivalente de un número romano especificado. Para obtener más información, por favor visite: http://nikiforov.developpez.com/espagnol/ ----------------------------------------------------------------------------------- For information about string UDFs in Portuguese language please visit the http://nikiforov.developpez.com/portugais/ ----------------------------------------------------------------------------------- For information about string UDFs in German language please visit the http://nikiforov.developpez.com/allemand/ ----------------------------------------------------------------------------------- For information about string UDFs in Italian language please visit the http://nikiforov.developpez.com/italien/ ----------------------------------------------------------------------------------- For information about string UDFs in Romanian language please visit the http://nikiforov.developpez.com/roumain/ ----------------------------------------------------------------------------------- For information about string UDFs in Russian language please visit the http://nikiforov.developpez.com/russe/ ----------------------------------------------------------------------------------- For information about string UDFs in Bulgarian language please visit the http://nikiforov.developpez.com/bulgare/ ----------------------------------------------------------------------------------- AT() User-Defined Function Returns the beginning numeric position of the first occurrence of a character expression within another character expression, counting from the leftmost character. AT similar to the Oracle function INSTR AT(@cSearchExpression, @cExpressionSearched [, @nOccurrence]) Return Values smallint Parameters @cSearchExpression nvarchar(4000) Specifies the character expression that AT( ) searches for in @cExpressionSearched. @cExpressionSearched nvarchar(4000) Specifies the character expression @cSearchExpression searches for. @nOccurrence smallint Specifies which occurrence (first, second, third, and so on) of @cSearchExpression is searched for in @cExpressionSearched. By default, AT() searches for the first occurrence of @cSearchExpression (@nOccurrence = 1). Including @nOccurrence lets you search for additional occurrences of @cSearchExpression in @cExpressionSearched. AT( ) returns 0 if @nOccurrence is greater than the number of times @cSearchExpression occurs in @cExpressionSearched. Remarks AT() searches the second character expression for the first occurrence of the first character expression. It then returns an integer indicating the position of the first character in the character expression found. If the character expression isn't found, AT() returns 0. The search performed by AT() is case-sensitive. Example declare @gcString nvarchar(4000), @gcFindString nvarchar(4000) select @gcString = 'Now is the time for all good men', @gcFindString = 'is the' select dbo.AT(@gcFindString, @gcString, default) -- Displays 5 set @gcFindString = 'IS' select dbo.AT(@gcFindString, @gcString, default) -- Displays 0, case-sensitive See Also RAT() User-Defined Function RAT( ) User-Defined Function Returns the numeric position of the last (rightmost) occurrence of a character string within another character string. RAT(@cSearchExpression, @cExpressionSearched [, @nOccurrence]) Return Values smallint Parameters @cSearchExpression nvarchar(4000) Specifies the character expression that RAT( ) looks for in @cExpressionSearched. @cExpressionSearched nvarchar(4000) Specifies the character expression that RAT() searches. @nOccurrence smallint Specifies which occurrence, starting from the right and moving left, of @cSearchExpression RAT() searches for in @cExpressionSearched. By default, RAT() searches for the last occurrence of @cSearchExpression (@nOccurrence = 1). If @nOccurrence is 2, RAT() searches for the next to last occurrence, and so on. Remarks RAT(), the reverse of the AT() function, searches the character expression in @cExpressionSearched starting from the right and moving left, looking for the last occurrence of the string specified in @cSearchExpression. RAT() returns an integer indicating the position of the first character in @cSearchExpression in @cExpressionSearched. RAT() returns 0 if @cSearchExpression isn't found in @cExpressionSearched, or if @nOccurrence is greater than the number of times @cSearchExpression occurs in @cExpressionSearched. The search performed by RAT() is case-sensitive. Example declare @gcString nvarchar(4000), @gcFindString nvarchar(4000) select @gcString = 'abracadabra', @gcFindString = 'a' select dbo.RAT(@gcFindString , @gcString, default) -- Displays 11 select dbo.RAT(@gcFindString , @gcString , 3) -- Displays 6 See Also AT() User-Defined Function OCCURS() User-Defined Function Returns the number of times a character expression occurs within another character expression (including overlaps). OCCURS(@cSearchExpression, @cExpressionSearched) Return Values smallint Parameters @cSearchExpression nvarchar(4000) Specifies a character expression that OCCURS() searches for within @cExpressionSearched. @cExpressionSearched nvarchar(4000) Specifies the character expression OCCURS() searches for @cSearchExpression. Remarks OCCURS() returns 0 (zero) if @cSearchExpression is not found within @cExpressionSearched. UDF the name and functionality of which correspond to the same built-in function of Visual FoxPro (but function OCCURS of Visual FoxPro counts the 'occurs' excluding overlaps !) Example declare @gcString nvarchar(4000) select @gcString = 'abracadabra' select dbo.OCCURS('a', @gcString ) -- Displays 5 select dbo.OCCURS('b', @gcString ) -- Displays 2 select dbo.OCCURS('c', @gcString ) -- Displays 1 select dbo.OCCURS('e', @gcString ) -- Displays 0 Attention, including overlaps !!! select dbo.OCCURS('ABCA', 'ABCABCABCA') -- Displays 3 1 occurrence of substring 'ABCA .. BCABCA' 2 occurrence of substring 'ABC...ABCA...BCA' 3 occurrence of substring 'ABCABC...ABCA' See Also AT(), RAT(), OCCURS2() OCCURS2() User-Defined Function Author: Stephen Dobson, Toronto Returns the number of times a character expression occurs within another character expression (excluding overlaps). OCCURS2(@cSearchExpression, @cExpressionSearched) Return Values smallint Parameters @cSearchExpression nvarchar(4000) Specifies a character expression that OCCURS2() searches for within @cExpressionSearched. @cExpressionSearched nvarchar(4000) Specifies the character expression OCCURS2() searches for @cSearchExpression. Remarks OCCURS2() returns 0 (zero) if @cSearchExpression is not found within @cExpressionSearched. Example 1 declare @gcString nvarchar(4000) select @gcString = 'abracadabra' select dbo.OCCURS2('a', @gcString ) -- Displays 5 select dbo.OCCURS2('b', @gcString ) -- Displays 2 Example 2 Counts the occurrences of differents characters from a string @gcCaracters in string @gcString declare @gcString nvarchar(4000), @gcCaracters nvarchar(256), @i smallint, @counter smallint select @i = 1, @counter = 0 select @gcString = N'For the most part, the remaining four hunters leaned on the table or lay in their bunks and left the discussion to the two antagonists. But they were supremely interested, for every little while they ardently took sides, and sometimes all were talking at once, till their voices surged back and forth in waves of sound like mimic thunder-rolls in the confined space. Childish and immaterial as the topic was, the quality of their reasoning was still more childish and immaterial. In truth, there was very little reasoning or none at all. Their method was one of assertion, assumption, and denunciation. They proved that a seal pup could swim or not swim at birth by stating the proposition very bellicosely and then following it up with an attack on the opposing man''s judgment, common sense, nationality, or past history. Rebuttal was precisely similar. I have related this in order to show the mental calibre of the men with whom I was thrown in contact. Intellectually they were children, inhabiting the physical forms of men.', @gcCaracters = N'abcca' while @i <= datalength(@gcCaracters)/2 begin if charindex(substring(@gcCaracters,@i,1), left(@gcCaracters, @i - 1)) = 0 select @counter = @counter + dbo.OCCURS2(substring(@gcCaracters,@i,1), @gcString) select @i = @i + 1 end select @counter -- Displays 92 Attention !!! This function counts the 'occurs' excluding overlaps ! select dbo.OCCURS2('ABCA', 'ABCABCABCA') -- Displays 2 1 occurrence of substring 'ABCA .. BCABCA' 2 occurrence of substring 'ABCABC... ABCA' UDF the functionality of which correspond to the built-in function OCCURS of Visual FoxPro See Also OCCURS() PADL(), PADR(), PADC() User-Defined Functions Returns a string from an expression, padded with spaces or characters to a specified length on the left or right sides, or both. PADL(@eExpression, @nResultSize [, @cPadCharacter]) -Or- PADR(@eExpression, @nResultSize [, @cPadCharacter]) -Or- PADC(@eExpression, @nResultSize [, @cPadCharacter]) Return Values nvarchar(4000) Parameters @eExpression nvarchar(4000) Specifies the expression to be padded. @nResultSize smallint Specifies the total number of characters in the expression after it is padded. @cPadCharacter nvarchar(4000) Specifies the value to use for padding. This value is repeated as necessary to pad the expression to the specified number of characters. If you omit @cPadCharacter, spaces (ASCII character 32) are used for padding. Remarks PADL() inserts padding on the left, PADR() inserts padding on the right, and PADC() inserts padding on both sides. PADL, PADR similar to the Oracle functions PL/SQL LPAD, RPAD Example declare @gcString nvarchar(4000) select @gcString = 'TITLE' select dbo.PADL(@gcString, 40, default) -- Displays ' TITLE' select dbo.PADL(@gcString, 40, '+=+') -- Displays '+=++=++=++=++=++=++=++=++=++=++=++=TITLE' select dbo.PADR(@gcString, 40, '=!!!=') -- Displays 'TITLE=!!!==!!!==!!!==!!!==!!!==!!!==!!!=' select dbo.PADC(@gcString, 40, '=*=') -- Displays '=*==*==*==*==*==*TITLE=*==*==*==*==*==*=' CHRTRAN() User-Defined Function 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 (@cExpressionSearched, @cSearchExpression, @cReplacementExpression) Return Values nvarchar Parameters @cSearchedExpression Specifies the expression in which CHRTRAN() replaces characters. @cSearchExpression Specifies the expression containing the characters CHRTRAN() looks for in @cSearchedExpression. @cReplacementExpression Specifies the expression containing the replacement characters. If a character in cSearchExpression is found in cSearchedExpression, the character in @cSearchedExpression is replaced by a character from @cReplacementExpression that is in the same position in @cReplacementExpression as the respective character in @cSearchExpression. If @cReplacementExpression has fewer characters than @cSearchExpression, the additional characters in @cSearchExpression are deleted from @cSearchedExpression. If @cReplacementExpression has more characters than @cSearchExpression, the additional characters in @cReplacementExpression are ignored. Remarks CHRTRAN() translates the character expression @cSearchedExpression using the translation expressions @cSearchExpression and @cReplacementExpression and returns the resulting character string. CHRTRAN similar to the Oracle function TRANSLATE. Example select dbo.CHRTRAN('ABCDEF', 'ACE', 'XYZ') -- Displays 'XBYDZF' select dbo.CHRTRAN('ABCDEF', 'ACE', 'XYZQRST') -- Displays 'XBYDZF' See Also STRFILTER() STRFILTER() User-Defined Function Removes all characters from a string except those specified. STRFILTER(@cExpressionSearched, @cSearchExpression) Return Values nvarchar Parameters @cExpressionSearched Specifies the character string to search. @cSearchExpression Specifies the characters to search for and retain in @cExpressionSearched. Remarks STRFILTER( ) removes all the characters from @cExpressionSearched that are not in @cSearchExpression, then returns the characters that remain. Example select dbo.STRFILTER('asdfghh5hh1jk6f3b7mn8m3m0m6','0123456789') -- Displays 516378306 select dbo.STRFILTER('ABCDABCDABCD', 'AB') -- Displays ABABAB See Also CHRTRAN() STRTRAN() User-Defined Function Searches a character expression for occurrences of a second character expression, and then replaces each occurrence with a third character expression. STRTRAN (@cSearched, @cExpressionSought , [@cReplacement][, @nStartOccurrence] [, @nNumberOfOccurrences] [, @nFlags]) Return Values nvarchar(4000) Parameters @cSearched Specifies the character expression that is searched. @cExpressionSought Specifies the character expression that is searched for in @cSearched. @cReplacement Specifies the character expression that replaces every occurrence of @cExpressionSought in @cSearched. If you omit @cReplacement, every occurrence of @cExpressionSought is replaced with the empty string. @nStartOccurrence Specifies which occurrence of @cExpressionSought is the first to be replaced. For example, if @nStartOccurrence is 4, replacement begins with the fourth occurrence of @cExpressionSought in @cSearched and the first three occurrences of @cExpressionSought remain unchanged. The occurrence where replacement begins defaults to the first occurrence of @cExpressionSought if you omit @nStartOccurrence. @nNumberOfOccurrences Specifies the number of occurrences of @cExpressionSought to replace. If you omit @nNumberOfOccurrences, all occurrences of @cExpressionSought, starting with the occurrence specified with @nStartOccurrence, are replaced. @nFlags Specifies the case-sensitivity of a search according to the following values: ----------------------------------------------------------------------------------------- @nFlags Description 0 (default) Search is case-sensitive, replace is with exact @cReplacement string. 1 Search is case-insensitive, replace is with exact @cReplacement string. 2 Search is case-sensitive; replace is with the case of @cReplacement changed to match the case of the string found. The case of @cReplacement will only be changed if the string found is all uppercase, lowercase, or proper case. 3 Search is case-insensitive; replace is with the case of @cReplacement changed to match the case of the string found. The case of @cReplacement will only be changed if the string found is all uppercase, lowercase, or proper case. ----------------------------------------------------------------------------------------- Remarks You can specify where the replacement begins and how many replacements are made. STRTRAN( ) returns the resulting character string. Specify –1 for optional parameters you want to skip over if you just need to specify the @nFlags setting. Example select dbo.STRTRAN('ABCDEF', 'ABC', 'XYZ',-1,-1,0) -- Displays XYZDEF select dbo.STRTRAN('ABCDEF', 'ABC', default,-1,-1,0) -- Displays DEF select dbo.STRTRAN('ABCDEFABCGHJabcQWE', 'ABC', default,2,-1,0) -- Displays ABCDEFGHJabcQWE select dbo.STRTRAN('ABCDEFABCGHJabcQWE', 'ABC', default,2,-1,1) -- Displays ABCDEFGHJQWE select dbo.STRTRAN('ABCDEFABCGHJabcQWE', 'ABC', 'XYZ', 2, 1, 1) -- Displays ABCDEFXYZGHJabcQWE select dbo.STRTRAN('ABCDEFABCGHJabcQWE', 'ABC', 'XYZ', 2, 3, 1) -- Displays ABCDEFXYZGHJXYZQWE select dbo.STRTRAN('ABCDEFABCGHJabcQWE', 'ABC', 'XYZ', 2, 1, 2) -- Displays ABCDEFXYZGHJabcQWE select dbo.STRTRAN('ABCDEFABCGHJabcQWE', 'ABC', 'XYZ', 2, 3, 2) -- Displays ABCDEFXYZGHJabcQWE select dbo.STRTRAN('ABCDEFABCGHJabcQWE', 'ABC', 'xyZ', 2, 1, 2) -- Displays ABCDEFXYZGHJabcQWE select dbo.STRTRAN('ABCDEFABCGHJabcQWE', 'ABC', 'xYz', 2, 3, 2) -- Displays ABCDEFXYZGHJabcQWE select dbo.STRTRAN('ABCDEFAbcCGHJAbcQWE', 'Aab', 'xyZ', 2, 1, 2) -- Displays ABCDEFAbcCGHJAbcQWE select dbo.STRTRAN('abcDEFabcGHJabcQWE', 'abc', 'xYz', 2, 3, 2) -- Displays abcDEFxyzGHJxyzQWE select dbo.STRTRAN('ABCDEFAbcCGHJAbcQWE', 'Aab', 'xyZ', 2, 1, 3) -- Displays ABCDEFAbcCGHJAbcQWE select dbo.STRTRAN('ABCDEFAbcGHJabcQWE', 'abc', 'xYz', 1, 3, 3) -- Displays XYZDEFXyzGHJxyzQWE See Also replace(), CHRTRAN() WORDTRAN() User-Defined Function WORDTRAN (@cSearched, @cWordSought , [@cWordReplacement] [, @cDelimiters] [, @nStartOccurrence] [, @nNombreOccurrences] [, @nFlags]) Searches a character string for occurrences of a first word, and then replaces each occurrence with a second word. Return Values nvarchar(4000) Parameters @cSearched Specifies the character string that is searched. @cWordSought Specifies the word that is searched for in @cSearched. @cWordReplacement Specifies the word that replaces every occurrence of @cWordSought in @cSearched. If you omit @cWordReplacement, every occurrence of @cWordSought is replaced with the empty string. @cDelimiters Optional. Specifies one or more optional characters used to separate words in @cSearched. The default delimiters are space, tab, carriage return, and line feed. The maximum length of @cDelimiters is 256. Note that WORDTRAN() uses each of the characters in @cDelimiters as individual delimiters, not the entire string as a single delimiter. @nStartOccurrence Specifies which occurrence of @cWordSought is the first to be replaced. For example, if @nStartOccurrence is 4, replacement begins with the fourth occurrence of @cWordSought in @cSearched and the first three occurrences of @cWordSought remain unchanged. The occurrence where replacement begins defaults to the first occurrence of @cWordSought if you omit @nStartOccurrence. @nNombreOccurrences Specifies the number of occurrences of @cWordSought to replace. If you omit @nNombreOccurrences, all occurrences of @cWordSought, starting with the occurrence specified with @nStartOccurrence, are replaced. @nFlags Specifies the case-sensitivity of a search according to the following values: ----------------------------------------------------------------------------------------- @nFlags Description 0 (default) Search is case-sensitive, replace is with exact @cWordReplacement word. 1 Search is case-insensitive, replace is with exact @cWordReplacement word. 2 Search is case-sensitive; replace is with the case of @cWordReplacement changed to match the case of the word found. The case of @cWordReplacement will only be changed if the word found is all uppercase, lowercase, or proper case. 3 Search is case-insensitive; replace is with the case of @cWordReplacement changed to match the case of the word found. The case of @cWordReplacement will only be changed if the word found is all uppercase, lowercase, or proper case. ----------------------------------------------------------------------------------------- Remarks You can specify where the replacement begins and how many replacements are made. WORDTRAN( ) returns the resulting character string. Specify default for optional parameters you want to skip over if you just need to specify the @nFlags setting. Examples select dbo.WORDTRAN('ABC DEF', 'ABC', 'XYZ', default, default, default, default) -- Displays XYZ DEF select dbo.WORDTRAN('ABC DEF', 'ABC', '123', default, -1, -1, default) -- Displays 123 DEF select dbo.WORDTRAN('ABC DEF ABC GHJabcQWE', 'ABC', Space(1), default, default, 1, 0) -- Displays DEF ABC GHJabcQWE select dbo.WORDTRAN('ABC DEF ABC GHJabcQWE', 'ABC', Space(1), default, default, 1, 0) -- Displays DEF ABC GHJabcQWE select dbo.WORDTRAN('ABC DEF ABC GHJabcQWE', 'ABC', 'XYZ', Space(1), 2, 1, 1) -- Displays ABC DEF XYZ GHJabcQWE select dbo.WORDTRAN('ABC DEF ABC GHJabcQWE', 'ABC', 'XYZ', Space(1), 2, 3, 1) -- Displays ABC DEF XYZ GHJabcQWE select dbo.WORDTRAN('ABC DEF ABC GHJabcQWE', 'ABC', 'XYZ', Space(1), 2, 1, 2) -- Displays ABC DEF XYZ GHJabcQWE select dbo.WORDTRAN('ABC DEF ABC GHJabcQWE', 'ABC', 'XYZ', Space(1), 2, 3, 2) -- Displays ABC DEF XYZ GHJabcQWE select dbo.WORDTRAN('ABC DEF ABC GHJabcQWE', 'ABC', 'xyZ', Space(1), 2, 1, 2) -- Displays ABC DEF XYZ GHJabcQWE select dbo.WORDTRAN('ABC DEF ABC GHJabcQWE', 'ABC', 'xYz', Space(1), 2, 3, 2) -- Displays ABC DEF XYZ GHJabcQWE select dbo.WORDTRAN('ABC DEF Abc CGHJAbcQWE', 'Aab', 'xyZ', Space(1), 2, 1, 2 ) -- Displays ABC DEF Abc CGHJAbcQWE select dbo.WORDTRAN('abc DEF abc GHJabcQWE', 'abc', 'xYz', Space(1), 3, 2, 0) -- Displays abc DEF abc GHJabcQWE select dbo.WORDTRAN('ABC DEF Abc CGHJAbcQWE', 'Aab', 'xyZ', Space(1), 2, 1, 3) -- Displays ABC DEF Abc CGHJAbcQWE select dbo.WORDTRAN('ABC DEF Abc GHJabcQWE', 'abc', 'xYz', Space(1), 1, 3, 3) -- Displays XYZ DEF Xyz GHJabcQWE -- Sometime the search for substring from a string will return the incorrect result, -- it is necessary to search for the word in the string. declare @lcAdmissibleCodes varchar(100), @lcCodeforReplacing varchar(100), @lcNewCode varchar(100) select @lcAdmissibleCodes = 'W,WN,IR,IU,I', @lcCodeforReplacing = 'I', @lcNewCode = 'A' select dbo.STRTRAN(@lcAdmissibleCodes, @lcCodeforReplacing, @lcNewCode, default, default, default) -- Displays W,WN,AR,AU,A select dbo.WORDTRAN(@lcAdmissibleCodes, @lcCodeforReplacing, @lcNewCode, ','+Space(1), default, default, default ) -- Displays W,WN,IR,IU,A select @lcCodeforReplacing = 'IR,' select dbo.WORDTRAN(@lcAdmissibleCodes, @lcCodeforReplacing, @lcNewCode, default, default, default, default) -- Displays W,WN,IR,IU,I select dbo.WORDTRAN(@lcAdmissibleCodes, @lcCodeforReplacing, @lcNewCode, ','+Space(1), default, default, default ) -- Displays W,WN,IR,IU,I See Also STRTRAN(), GETATWORD(), GETRATWORD(), GETOCCURSWORD(), GETNUMWORD(), GETWORDNUM() , GETWORDCOUNT(), GETALLWORDS(), GETALLWORDS2() GETWORDCOUNT() User-Defined Function Counts the words in a string. GETWORDCOUNT(@cString[, @cDelimiters]) Return Value smallint Parameters @cString nvarchar(4000) - Specifies the string whose words will be counted. @cDelimiters nvarchar(256) - Optional. Specifies one or more optional characters used to separate words in @cString. The default delimiters are space, tab, carriage return, and line feed. Note that GETWORDCOUNT( ) uses each of the characters in @cDelimiters as individual delimiters, not the entire string as a single delimiter. Remarks GETWORDCOUNT() by default assumes that words are delimited by spaces or tabs. If you specify another character as delimiter, this function ignores spaces and tabs and uses only the specified character. Example If you use 'To be, or not to be: that is the question:' as the target string for dbo.GETWORDCOUNT(), you can get all the following results. declare @cString nvarchar(4000) set @cString = 'To be, or not to be: that is the question:' select dbo.GETWORDCOUNT(@cString, default) -- Displays 10 - character groups, delimited by ' ' select dbo.GETWORDCOUNT(@cString, ',') -- Displays 2 - character groups, delimited by ',' See Also GETWORDNUM() User-Defined Function GETWORDNUM() User-Defined Function Returns a specified word from a string. GETWORDNUM(@cString, @nIndex[, @cDelimiters]) Return Value nvarchar(4000) Parameters @cString nvarchar(4000) - Specifies the string to be evaluated @nIndex smallint - Specifies the index position of the word to be returned. For example, if @nIndex is 3, GETWORDNUM( ) returns the third word (if @cString contains three or more words). @cDelimiters nvarchar(256) - Optional. Specifies one or more optional characters used to separate words in @cString. The default delimiters are space, tab, carriage return, and line feed. Note that GetWordNum( ) uses each of the characters in @cDelimiters as individual delimiters, not the entire string as a single delimiter. Remarks Returns the word at the position specified by @nIndex in the target string, @cString. If @cString contains fewer than @nIndex words, GETWORDNUM( ) returns an empty string. Example declare @cString nvarchar(4000) set @cString = 'Whether ''tis nobler in the mind to suffer' select dbo.GETWORDNUM(@cString, 3, default) -- Displays 'nobler' See Also GETWORDCOUNT() User-Defined Function GETNUMWORD() User-Defined Function Returns the index position of a word in a string GETNUMWORD(@cString, @cWord[, @cDelimiters] [, @nOccurrence] [, @nFlags]) Return Value smallint Returns the index position of a word in a string.. If @cString don't contain the word cWord, GETNUMWORD( ) returns 0. This function may be very useful to know if a word exists in a string or not. Parameters @cString nvarchar(4000) - Specifies the string to be evaluated @cWord nvarchar(4000) - Specifies the word to search for in @cString. @cDelimiters nvarchar(256) - Optional. Specifies one or more optional characters used to separate words in @cString. The default delimiters are space, tab, carriage return, and line feed. Note that GETNUMWORD( ) uses each of the characters in @cDelimiters as individual delimiters, not the entire string as a single delimiter. @nOccurrence smallint Optional Specifies which occurrence, first, second, third, and so on, of @cWord to search for in @cString. By default, GETNUMWORD( ) searches for the first occurrence of @cWord (@nOccurrence = 1). @nFlags bit Optional Specifies Case-sensitive search criteria to apply to this function. Valid values are 0 (the default) and 1. 0 Case-sensitive search 1 Case insensitive search Example select dbo.GETNUMWORD('O Canada! Our home and native land!', 'Canada!', default, 1, 0) -- Displays 2 -- Sometime the search for substring from a string will return the incorrect result, -- it is necessary to search for the word in the string. declare @lcAdmissibleCodes nvarchar(4000), @lcCodeforChecking nvarchar(4000) set @lcAdmissibleCodes = 'W,WN,IR,IU' set @lcCodeforChecking = 'I' select charindex(@lcCodeforChecking, @lcAdmissibleCodes) -- Displays 6 select dbo.GETNUMWORD(@lcAdmissibleCodes, @lcCodeforChecking, ',' + Space(1), default, default) -- Displays 0 set @lcCodeforChecking = 'IR,' select charindex(@lcCodeforChecking, @lcAdmissibleCodes) -- Displays 6 select dbo.GETNUMWORD(@lcAdmissibleCodes, @lcCodeforChecking, ',' + Space(1), default, default) -- Displays 0 set @lcAdmissibleCodes = 'W,WN,IR,IU,WN,DS' set @lcCodeforChecking = 'WN' select dbo.GETNUMWORD(@lcAdmissibleCodes, @lcCodeforChecking, ',' + Space(1), 2, default) -- Displays 5 GETALLWORDS() User-Defined Function Inserts the words from a string into the table. GETALLWORDS(@cString[, @cDelimiters]) Return Value table @GETALLWORDS (WORDNUM smallint, WORD nvarchar(4000), STARTOFWORD smallint, LENGTHOFWORD smallint) Parameters @cString nvarchar(4000) - Specifies the string whose words will be inserted into the table @GETALLWORDS. @cDelimiters nvarchar(256) - Optional. Specifies one or more optional characters used to separate words in @cString. The default delimiters are space, tab, carriage return, and line feed. Note that GETALLWORDS( ) uses each of the characters in @cDelimiters as individual delimiters, not the entire string as a single delimiter. Remarks GETALLWORDS() by default assumes that words are delimited by spaces or tabs. If you specify another character as delimiter, this function ignores spaces and tabs and uses only the specified character. Example declare @cString nvarchar(4000) set @cString = 'The default delimiters are space, tab, carriage return, and line feed. If you specify another character as delimiter, this function ignores spaces and tabs and uses only the specified character.' select * from dbo.GETALLWORDS(@cString, default) select * from dbo.GETALLWORDS(@cString, ' ,.') See Also GETWORDNUM(), GETWORDCOUNT(), GETALLWORDS2() User-Defined Functions GETALLWORDS2() User-Defined Function Inserts the words from a string into the table. GETALLWORDS2(@cString[, @cStringSplitting]) Return Value table @GETALLWORDS2 (WORDNUM smallint, WORD nvarchar(4000), STARTOFWORD smallint, LENGTHOFWORD smallint) Parameters @cString nvarchar(4000) - Specifies the string whose words will be inserted into the table @GETALLWORDS2. @cStringSplitting nvarchar(256) - Optional. Specifies the string used to separate words in @cString. The default delimiter is space. Note that GETALLWORDS2( ) uses the entire string @cStringSplitting as a single delimiter. Remarks GETALLWORDS2() by default assumes that words are delimited by space. If you specify another string as a single delimiter, this function ignores space and uses only the specified string as a single delimiter. Example declare @cString nvarchar(4000), @nIndex smallint select @cString = 'We hold these truths to be self-evident, that all men are created equal, that they are endowed by their Creator with certain unalienable Rights, that among these are Life, Liberty and the pursuit of Happiness.', @nIndex = 30 select WORD from dbo.GETALLWORDS2(@cString, default) where WORDNUM = @nIndex -- Displays ‘Liberty’ select top 1 WORDNUM from dbo.GETALLWORDS2(@cString, default) order by WORDNUM desc -- Displays 35 See Also GETWORDNUM(), GETWORDCOUNT(), GETALLWORDS2() User-Defined Functions The accuracy criteria of user-defined functions GETWORDNUM() , 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. GETOCCURSWORD (@cString, @cWord[, @cDelimiters] [, @nFlags]) Returns the number of times a word occurs within the character string. If @cString don't contain the word @cWord, GETOCCURSWORD() returns 0. Return Value smallint Parameters @cString Specifies the string to be evaluated @cWord Specifies the word to search for in @cString. @cDelimiters Optional. Specifies one or more optional characters used to separate words in @cString. The default delimiters are space, tab, carriage return, and line feed. The maximum length of @cDelimiters is 256. Note that GETOCCURSWORD() uses each of the characters in @cDelimiters as individual delimiters, not the entire string as a single delimiter. @nFlags Specifies Case-sensitive search criteria to apply to this function. Valid Values are 0 (the Default) And 1. 0 Case-sensitive search 1 Case insensitive search In order to accommodate the @nFlags parameter, you will need to bypass the optional parameters by passing a value of default. Examples: declare @lcString as nvarchar(1000) select @lcString = 'O Canada! Our home and native land! True patriot love in all thy sons command. With glowing hearts we see thee rise, The True North strong and free! From far and wide, O Canada, we stand on guard for thee. God keep our land glorious and free! O Canada, we stand on guard for thee. O Canada, we stand on guard for thee.' select dbo.GETOCCURSWORD(@lcString, 'Canada', ' ,.!?', default) -- Displays 4 -- Sometime the search for substring from a string will return the incorrect result, -- it is necessary to search for the word in the string. declare @lcAdmissibleCodes nvarchar(4000), @lcCodeforChecking nvarchar(4000) set @lcAdmissibleCodes = 'W,WN,IR,IU' set @lcCodeforChecking = 'I' select dbo.OCCURS(@lcCodeforChecking, @lcAdmissibleCodes) -- Displays 2 select dbo.GETOCCURSWORD(@lcAdmissibleCodes, @lcCodeforChecking, ',' + Space(1), default) -- Displays 0 set @lcCodeforChecking = 'IR,' select dbo.OCCURS(@lcCodeforChecking, @lcAdmissibleCodes) -- Displays 1 select dbo.GETOCCURSWORD(@lcAdmissibleCodes, @lcCodeforChecking, ',' + Space(1), default) -- Displays 0 set @lcAdmissibleCodes = 'W,WN,IR,IU,WN,DS' set @lcCodeforChecking = 'WN' select dbo.GETOCCURSWORD(@lcAdmissibleCodes, @lcCodeforChecking, ',' + Space(1), default) -- Displays 2 See Also GETATWORD(), WORDTRAN(), GETNUMWORD(), GETWORDNUM() , GETWORDCOUNT(), GETALLWORDS(), GETALLWORDS2() GETATWORD (@cString, @cWord[, @cDelimiters] [, @nOccurrence] [, @nFlags]) Returns the beginning numeric position of the nth occurrence of a word within the character string, counting from the leftmost character. If @cString don't contain the word @cWord, GETATWORD() returns 0. Return Value smallint Parameters @cString Specifies the string to be evaluated @cWord Specifies the word to search for in @cString. @cDelimiters Optional. Specifies one or more optional characters used to separate words in @cString. The default delimiters are space, tab, carriage return, and line feed. The maximum length of @cDelimiters is 256. Note that GETATWORD() uses each of the characters in @cDelimiters as individual delimiters, not the entire string as a single delimiter. @nOccurrence Specifies which occurrence, first, second, third, and so on, of @cWord to search for in @cString. By default, GETATWORD( ) searches for the first occurrence of @cWord (@nOccurrence = 1). @nFlags Specifies Case-sensitive search criteria to apply to this function. Valid Values are 0 (the Default) And 1. 0 Case-sensitive search 1 Case insensitive search In order to accommodate the @nFlags parameter, you will need to bypass the optional parameters by passing a value of default. Examples: select dbo.GETATWORD('Ceterum censeo Carthaginem esse delendam', 'Carthaginem', default, default, default) -- Displays 16 -- Sometime the search for substring from a string will return the incorrect result, -- it is necessary to search for the word in the string. declare @lcAdmissibleCodes nvarchar(4000), @lcCodeforChecking nvarchar(4000) set @lcAdmissibleCodes = 'W,WN,IR,IU' set @lcCodeforChecking = 'I' select dbo.AT(@lcCodeforChecking, @lcAdmissibleCodes, default) -- Displays 6 select dbo.GETATWORD(@lcAdmissibleCodes, @lcCodeforChecking, ',' + Space(1), default, default) -- Displays 0 set @lcCodeforChecking = 'IR,' select dbo.AT(@lcCodeforChecking, @lcAdmissibleCodes, default) -- Displays 6 select dbo.GETATWORD(@lcAdmissibleCodes, @lcCodeforChecking, ',' + Space(1), default, default) -- Displays 0 set @lcAdmissibleCodes = 'W,WN,IR,IU,WN,DS' set @lcCodeforChecking = 'WN' select dbo.GETATWORD(@lcAdmissibleCodes, @lcCodeforChecking, ',' + Space(1), 2, default) -- Displays 12 See Also GETRATWORD(), WORDTRAN(), GETOCCURSWORD(), GETATWORD(), GETWORDNUM() , GETWORDCOUNT(), GETALLWORDS(), GETALLWORDS2() PROPER( ) User-Defined Function Returns from a character expression a string capitalized as appropriate for proper names. PROPER similar to the Oracle function INITCAP PROPER(@cExpression) Return Values nvarchar(4000) Parameters @cExpression nvarchar(4000) Specifies the character expression from which PROPER( ) returns a capitalized character string. Example declare @gcExpr1 nvarchar(4000), @gcExpr2 nvarchar(4000) select @gcExpr1 = 'Visual Basic.NET', @gcExpr2 = 'VISUAL BASIC.NET' select dbo.PROPER(@gcExpr1) -- Displays 'Visual Basic.net' select dbo.PROPER(@gcExpr2) -- Displays 'Visual Basic.net' ARABTOROMAN() Returns the character Roman numeral equivalent of a specified numeric expression ARABTOROMAN(@tNum) Return Values varchar(15) Parameters @tNum number select dbo.ARABTOROMAN(3888) -- Displays MMMDCCCLXXXVIII ROMANTOARAB() Returns the number equivalent of a specified character Roman numeral expression ROMANTOARAB(@tcRomanNumber) Return Values smallint Parameters @tcRomanNumber varchar(15) Roman number Example select dbo.ROMANTOARAB('MDCCCLXXXVIII') -- Displays 1888 ----------------------------------------------------------------------------------- User-Defined Functions Transact-SQL, the name and functionality of which correspond to the same built-in functions of Visual FoxPro ----------------------------------------------------------------------------------- Testimonials: 1) 'Tahnks Igor! Super!', 'GREAT JOB Igor. Best regards and thank you again for these functions' Borislav Borissov, Bulgaria 2) 'Thank you Igor, these will be interesting to try' Kirk Kelly, USA 3) 'Good post,bravo!' Steven Wang, China 4) 'Je constate qu'ils sont très utiles et bien écrits …' Linda Carpenter, USA 5) 'Merci beaucoup. C'est vraiment une très bonne idée que de proposer dans ce forum des routines comme celles-ci. Cela nous permettra d'enrichir très vite une bibliothèque commune' Med Bouchenafa 6) 'c'est intéressant de poster ces propositions, pour augmenter l'efficacité, il est aussi possible de les envoyer à : sqlwish@microsoft.com' Bruno Reiter [MVP] 'c'est un avis personnel et pas en tant que MVP' 7) 'Merci pour cette contribution' Richard Flouriot 8) 'Bravo Igor! Super' Steven Black 9) 'Thank you for sharing your code. I Googled 'SQL count occurrence of character in a string' and bingo! Didn't have to write it and got so much more. Appreciate it.' Phil Youker, Programmer/Analyst, VCSSO/Information Technology Services 10) 'Igor, Thanks for your fantastic contribution to the community, they are very helpful.' Satya SKJ 11) 'Com certeza pode ser muito útil em determinados momentos. abraços ' Nilton Pinheiro , Brazil 12I) 'Gracias Igor, son de gran utilidad esas funciones. Muchísimos saludos' Gustavo Larriera, MVP, Uruguay 13) 'there once was a clever string function that came to be hyperlinked with gumption it was linked too often by a self proclaimed boffin who's translation was not fit for consumption' Damian, The Great Australian poet, Sydney, Australia 14) 'Thanks for the links' Madhivanan, India 15) 'Hi Igor, I found strfilter as good handy function. Thanx alot' Jain, India 16) 'Excellent. Thank you for sharing your UDF.' Surendra Kalekar, India 17) 'Thanks for the UDF’s. Great work.' Arthur Hill, CPA, MCSD, Programmer, Dearborn, MI, USA 18) 'you deserve a star for your efforts.' NoCoolHandle (Programmer) 19) 'Igor, I think You are obsessed with string manipulation functions. How about contributing on some of the questions / problems that people have? STRTRAN is nice, but I have yet to find the problem it solves.' rockmoose, Stockholm, Sweden 20) 'Hola Igor, gracias por ello, ...' Maxi, Buenos Aires, Argentina 21) 'J'ai déjà eu l'occasion de voir ces fonctions. Elles sont de bonne qualité.' Romelard Fabrice, France. 22) 'Gracias a Vos Igor.' Christian Gutman, Buenos Aires, Argentina 23) ‘Hi Igor, I just found your function on http://wiki.ittoolbox.com/index.php/Code:User-Defined_string_Functions_Transact-SQL_MS_SQL_Server for padding fields. From using the LPAD in Oracle before I just couldn't believe there wasn't a similar built in function in MS to do this. It was only after searching the net I found a few posts saying there wasn't. I used your function as the basis for what I needed to do so thanks for that, it saved me a bit of time. Regards’ Craig, Great Britain 24) ’Hello Igor, thank you very much for these handy UDF's.’ Shaun St. Louis, USA 25) ’Igor, I found your sample functions. They are very good …’ Sofia Pustilnik, USA 26) ‘just stumbled onto your t-sql functions - WOW - I have been doing a few other things to avoid doing these EXACT things. It will save me a HUGE amount of time. I appreciate this so much - it is already paying dividends in the first 30 minutes of downloading them to my 2005 box.’ Jim Woodin, USA ___________________________________________ I express my gratitude to the following donors for appreciating my work and for their monetary contribution: (I will place a link to a donor's website if requested.) 1. Mr. James Woodin, Iowa, USA ----------------------------------------------------------------------------------- Mesdames et Messieurs, J'aimerais vous remercier pour l’avis favorable au sujet de mes fonctions. Veuillez agréer, Mesdames et Messieurs, l'expression de mes meilleurs sentiments. J'aimerais surtout remercier Madame Nadia Slejskova pour la traduction du texte en anglais et en tchèque, Madame Linda Carpenter pour la correction du texte en anglais, Monsieur Guy Bonemme pour la correction du texte en français, Madame Ana María Bisbé York pour la traduction du texte en espagnol (www.amby.net), Monsieur Pablo Jorge Martinez pour la traduction du texte en espagnol, Madame Liliana Chiea Broch pour la traduction du texte en italien et en espagnol, Madame Fernanda Goto Goldenberg pour la traduction du texte en portugais, Monsieur Uwe Habermann pour la traduction du texte en allemand, Monsieur Borislav Borissov pour la traduction du texte en bulgare, Madame Cornelia Mateias pour la traduction du texte en roumain, Madame Yan Fang Wang et Monsieur Ye Fang pour la traduction du texte en chinois. Igor Nikiforov
Created by
Igor Nikiforov, Belron Canada inc.

Comments
I don't care that this is now ten years old. The chrtran() and a dozen others are STILL missing in TSQL... things that VFP had in the last century! Dragan Nedeljkovich, January 31, 2015

Add a comment
More downloads created from this member
St_Denis library of User-defined string functions Visual Studio.Net
Igor Nikiforov, Belron Canada inc.
The St_Denis library is available for VB.Net, C#.Net and C++.Net . In total the library has about 30 functions. In my humble opinion, it will be convenient to apply such functions for developing applications.
Created on
14 years ago
Downloads
1127
File type
Freeware
AGETFILESUMMARY is the same thing as AGETFILEVERSION but for Summary resources.
Igor Nikiforov, Belron Canada inc.
AGETFILESUMMARY is the same thing as AGETFILEVERSION but for Summary resources. the code from http://support.microsoft.com/kb/186898/en-us has been adapted for VFP fll library by Igor Nikiforov 2008-11-12 Creates an array containing information about files with summary resources or about Compo...
Created on
15 years ago
Downloads
913
File type
Freeware
User-Defined string functions Visual Basic
Igor Nikiforov, Belron Canada inc.
User-Defined string functions Visual Basic STRTRANEX, WORDTRANEX, AT, RAT, OCCURS, PADC, PADR, PADL, CHRTRAN, STRFILTER, STRTRAN, WORDTRAN, GETWORDCOUNT, GETWORDNUM, GETNUMWORD, GETALLWORDS, ARABTOROMAN, ROMANTOARAB etc. In my humble opinion, it will be convenient to apply such functions for writin...
Created on
16 years ago
Downloads
1602
File type
Freeware
St_Denis library of User-defined functions LCK VFP
Igor Nikiforov, Belron Canada inc.
The ST_DENIS library is available for all versions Visual FoxPro through 9.0, FoxPro 2.6 for DOS and FoxPro 2.6 for Windows. In total the library has about 25 functions. In my humble opinion, it will be convenient to apply such functions for developing applications. Here is the list of functions ...
Created on
17 years ago
Downloads
3826
File type
Freeware
UDFs T-SQL CLR (VB. Net, C#.Net, C++. Net)
Igor Nikiforov, Belron Canada inc.
User-Defined string functions Transact-SQL MS SQL Server 2005, 2008 Common Language Runtime CLR (VB. Net, C#.Net, C++. Net) with source code. AT RAT OCCURS PADC PADR PADL CHRTRAN STRFILTER STRTRAN WORDTRAN GETWORDCOUNT GETWORDNUM GETNUMWORD GETALLWORDS GETOCCURSWORD GETATWORD GETRATWORD PROPER RC...
Created on
18 years ago
Downloads
6475
File type
Freeware
User-Defined string functions Oracle PL/SQL
Igor Nikiforov, Belron Canada inc.
User-Defined string functions Oracle PL/SQL GETALLWORDS, GETWORDCOUNT, GETWORDNUM, OCCURS, PADC, STRTRAN, STRFILTER, ATC, RAT, ROMANTOARAB etc. In my humble opinion, it will be convenient to apply such functions for writing of stored procedures and User-Defined Functions on Oracle Procedural Lang...
Created on
18 years ago
Downloads
3436
File type
Freeware
User-Defined string functions SQL PL DB2
Igor Nikiforov, Belron Canada inc.
User-Defined string functions SQL Procedural Language DB2 AT, RAT, OCCURS, PADC, PADR, PADL, CHRTRAN, STRTRAN, STRFILTER, GETWORDCOUNT, GETWORDNUM, GETALLWORDS, PROPER, RCHARINDEX, ARABTOROMAN, ROMANTOARAB etc. In my humble opinion, it will be convenient to apply such functions for writing of st...
Created on
18 years ago
Downloads
2556
File type
Freeware
User-Defined string functions Transact-SQL Sybase
Igor Nikiforov, Belron Canada inc.
User-Defined string functions Transact-SQL Sybase ASA AT, RAT, OCCURS, PADC, PADR, PADL, CHRTRAN, STRTRAN, STRFILTER, GETWORDCOUNT, GETWORDNUM, GETALLWORDS, PROPER, RCHARINDEX, ARABTOROMAN, ROMANTOARAB etc. In my humble opinion, it will be convenient to apply such functions for writing of stored p...
Created on
18 years ago
Downloads
2798
File type
Freeware