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

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 procedures and User-Defined Functions on Transact-SQL Sybase ASA. Plus, there are CHM files in English, French, Spanish, German and Russian.
Created on
18 years ago
Downloads
2798
File type
Freeware
General information
URL:
UDFs_Sybase.zip
Rating:
Rate this item
Description
Keywords: User-Defined string functions Transact-SQL Transact SQL UDFs UDF SQL Sybase ASA function T-SQL similar function Oracle PL/SQL DB2 INSTR LPAD RPAD RPAD TRANSLATE INITCAP VFP Visual FoxPro Fox AT RAT OCCURS PADC PADR PADL CHRTRAN STRTRAN STRFILTER GETWORDCOUNT GETWORDNUM GETALLWORDS PROPER RCHARINDEX ARABTOROMAN ROMANTOARAB 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 ----------------------------------------------------------------------------------- User-Defined string functions Transact-SQL: AT, RAT, OCCURS, PADC, PADR, PADL, CHRTRAN, STRTRAN, STRFILTER, GETWORDCOUNT, GETWORDNUM, 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 Sybase ASA. 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 Sybase database, open Interactive SQL, open the file create_udfs_functions.sql, and execute it by pressing F5. To delete the given function, open Interactive SQL, open the file drop_udfs_functions.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. 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). ----------------------------------------------------------------------------------- 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. 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. STRFILTER(): Enlève tous les caractères d'une chaîne sauf ceux qui sont spécifiés. GETWORDCOUNT(): Renvoie le nombre de mots à l'intérieur d'une chaîne. GETWORDNUM(): Renvoie un mot spécifié d'une chaîne. GETALLWORDS(): Insère tous les mots d'une chaîne dans la table. 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. 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). STRFILTER(): Quita todos los carácteres de una cadena de carácteres excepto aquellos especificados. GETWORDCOUNT(): Cuenta el número de palabras de una cadena. GETWORDNUM(): Devuelve la palabra especificada de una cadena. GETALLWORDS(): Inserta las palabras de una cadena en la tabla. 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 (including overlaps). AT similar to the Oracle function INSTR AT(@cSearchExpression, @cExpressionSearched [, @nOccurrence]) Return Values smallint Parameters @cSearchExpression varchar(32767) Specifies the character expression that AT( ) searches for in @cExpressionSearched. @cExpressionSearched varchar(32767) 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 is’t found, AT() returns 0. The search performed by AT() is case-sensitive. Example declare @gcString varchar(32767), @gcFindString varchar(32767) select @gcString = 'Now is the time for all good me’, @gcFindString = 'is the' select DBA.AT(@gcFindString, @gcString) -- Displays 5 set @gcFindString = 'IS' select DBA.AT(@gcFindString, @gcString) -- Displays 0, case-sensitive See Also RAT(), ATC(), AT2(), RATC() User-Defined Functions RAT( ) User-Defined Function Returns the numeric position of the last (rightmost) occurrence of a character string within another character string (including overlaps). RAT(@cSearchExpression, @cExpressionSearched [, @nOccurrence]) Return Values smallint Parameters @cSearchExpression varchar(32767) Specifies the character expression that RAT( ) looks for in @cExpressionSearched. @cExpressionSearched varchar(32767) 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 is’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 varchar(32767), @gcFindString varchar(32767) select @gcString = 'abracadabra', @gcFindString = 'a' select DBA.RAT(@gcFindString , @gcString) -- Displays 11 select DBA.RAT(@gcFindString , @gcString , 3) -- Displays 6 See Also AT(), RATC() 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 varchar(32767) Specifies a character expression that OCCURS() searches for within @cExpressionSearched. @cExpressionSearched varchar(32767) 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 varchar(32767) select @gcString = 'abracadabra' select DBA.OCCURS('a', @gcString ) -- Displays 5 select DBA.OCCURS('b', @gcString ) -- Displays 2 select DBA.OCCURS('c', @gcString ) -- Displays 1 select DBA.OCCURS('e', @gcString ) -- Displays 0 Attention, including overlaps !!! select DBA.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() ___________________________________________ 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 varchar(32767) Parameters @eExpression varchar(32767) Specifies the expression to be padded. @nResultSize smallint Specifies the total number of characters in the expression after it is padded. @cPadCharacter varchar(32767) 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 varchar(32767) select @gcString = 'TITLE' select DBA.PADL(@gcString, 40) -- Displays ' TITLE' select DBA.PADL(@gcString, 40, '+=+') -- Displays '+=++=++=++=++=++=++=++=++=++=++=++=TITLE' select DBA.PADR(@gcString, 40, '=!!!=') -- Displays 'TITLE=!!!==!!!==!!!==!!!==!!!==!!!==!!!=' select DBA.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 varchar(32767) 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 DBA.CHRTRAN('ABCDEF', 'ACE', 'XYZ') -- Displays 'XBYDZF' select DBA.CHRTRAN('ABCDEF', 'ACE', 'XYZQRST') -- Displays 'XBYDZF' See Also STRFILTER() 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 varchar(32767) 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 DBA.STRTRAN('ABCDEF', 'ABC', 'XYZ',-1,-1,0) -- Displays XYZDEF select DBA.STRTRAN('ABCDEF', 'ABC', -1,-1,-1,0) -- Displays DEF select DBA.STRTRAN('ABCDEFABCGHJabcQWE', 'ABC', -1,2,-1,0) -- Displays ABCDEFGHJabcQWE select DBA.STRTRAN('ABCDEFABCGHJabcQWE', 'ABC', -1,2,-1,1) -- Displays ABCDEFGHJQWE select DBA.STRTRAN('ABCDEFABCGHJabcQWE', 'ABC', 'XYZ', 2, 1, 1) -- Displays ABCDEFXYZGHJabcQWE select DBA.STRTRAN('ABCDEFABCGHJabcQWE', 'ABC', 'XYZ', 2, 3, 1) -- Displays ABCDEFXYZGHJXYZQWE select DBA.STRTRAN('ABCDEFABCGHJabcQWE', 'ABC', 'XYZ', 2, 1, 2) -- Displays ABCDEFXYZGHJabcQWE select DBA.STRTRAN('ABCDEFABCGHJabcQWE', 'ABC', 'XYZ', 2, 3, 2) -- Displays ABCDEFXYZGHJabcQWE select DBA.STRTRAN('ABCDEFABCGHJabcQWE', 'ABC', 'xyZ', 2, 1, 2) -- Displays ABCDEFXYZGHJabcQWE select DBA.STRTRAN('ABCDEFABCGHJabcQWE', 'ABC', 'xYz', 2, 3, 2) -- Displays ABCDEFXYZGHJabcQWE select DBA.STRTRAN('ABCDEFAbcCGHJAbcQWE', 'Aab', 'xyZ', 2, 1, 2) -- Displays ABCDEFAbcCGHJAbcQWE select DBA.STRTRAN('abcDEFabcGHJabcQWE', 'abc', 'xYz', 2, 3, 2) -- Displays abcDEFxyzGHJxyzQWE select DBA.STRTRAN('ABCDEFAbcCGHJAbcQWE', 'Aab', 'xyZ', 2, 1, 3) -- Displays ABCDEFAbcCGHJAbcQWE select DBA.STRTRAN('ABCDEFAbcGHJabcQWE', 'abc', 'xYz', 1, 3, 3) -- Displays XYZDEFXyzGHJxyzQWE See Also replace(), CHRTRAN() STRFILTER() User-Defined Function Removes all characters from a string except those specified. STRFILTER(@cExpressionSearched, @cSearchExpression) Return Values varchar(32767) 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 DBA.STRFILTER('asdfghh5hh1jk6f3b7mn8m3m0m6','0123456789') -- Displays 516378306 select DBA.STRFILTER('ABCDABCDABCD', 'AB') -- Displays ABABAB See Also CHRTRAN() User-Defined Function ___________________________________________ GETWORDCOUNT() User-Defined Function Counts the words in a string. GETWORDCOUNT(@cString[, @cDelimiters]) Return Value smallint Parameters @cString varchar(32767) - Specifies the string whose words will be counted. @cDelimiters varchar(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 DBA.GETWORDCOUNT(), you can get all the following results. declare @cString varchar(32767) set @cString = 'To be, or not to be: that is the question:' select DBA.GETWORDCOUNT(@cString) -- Displays 10 - character groups, delimited by ' ' select DBA.GETWORDCOUNT(@cString, ',') -- Displays 2 - character groups, delimited by ',' See Also GETWORDNUM() User-Defined Function GETALLWORDS2, GETALLWORDS Stored Procedures GETWORDNUM() User-Defined Function Returns a specified word from a string. GETWORDNUM(@cString, @nIndex[, @cDelimiters]) Return Value varchar(32767) Parameters @cString varchar(32767) - 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 varchar(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 varchar(32767) set @cString = 'Whether ''tis nobler in the mind to suffer' select DBA.GETWORDNUM(@cString, 3) -- Displays 'nobler' See Also GETWORDCOUNT() User-Defined Function GETALLWORDS2, GETALLWORDS Stored Procedures GETALLWORDS Stored Procedure Inserts the words from a string into the table. GETALLWORDS @cString[, @cDelimiters] Creates the table @GETALLWORDS (WORDNUM smallint, WORD varchar(32767), STARTOFWORD smallint, LENGTHOFWORD smallint) Parameters @cString varchar(32767) - Specifies the string whose words will be inserted into the table @GETALLWORDS. @cDelimiters varchar(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 procedure ignores spaces and tabs and uses only the specified character. Example declare @cString varchar(32767) set @cString = 'The default delimiters are space, tab, carriage return, and line feed. If you specify another character as delimiter, this procedure ignores spaces and tabs and uses only the specified character.' exec DBA.GETALLWORDS @cString select * from @GETALLWORDS exec DBA.GETALLWORDS @cString, ' ,.' select * from @GETALLWORDS See Also GETWORDNUM() , GETWORDCOUNT() User-Defined Functions GETALLWORDS2 Stored Procedure 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. ___________________________________________ 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 varchar(32767) Parameters @cExpression varchar(32767) Specifies the character expression from which PROPER( ) returns a capitalized character string. Example declare @gcExpr1 varchar(32767), @gcExpr2 varchar(32767) select @gcExpr1 = 'Visual Basic.NET', @gcExpr2 = 'VISUAL BASIC.NET' select DBA.PROPER(@gcExpr1) -- Displays 'Visual Basic.net' select DBA.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 DBA.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 DBA.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 ___________________________________________ 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
Created by
Igor Nikiforov, Belron Canada inc.

Comments
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
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 ...
Created on
19 years ago
Downloads
17535
File type
Freeware
Rating
5.00/5.00