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

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 stored procedures and User-Defined Functions on SQL Procedural Language DB2. Plus, there are CHM files in English, French, Spanish, German and Russian.
Created on
18 years ago
Downloads
2556
File type
Freeware
General information
URL:
UDFs_SQL_PL_DB2.zip
Rating:
Rate this item
Description
Keywords:DB2 IBM User-Defined string functions SQL PL Procedural Language UDFs UDF function similar function Oracle PL/SQL Sybase ASA 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 recursive recursion 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 Oracle PL/SQL Sybase ASA MS SQL Server Visual Extend Framework VFX dFPUG ----------------------------------------------------------------------------------- User-Defined string functions SQL Procedural Language DB2 : 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 SQL Procedural Language DB2. AT,( ATC, AT2,) PADL, PADR, CHRTRAN, PROPER similar to the Oracle functions INSTR, LPAD, RPAD, TRANSLATE, INITCAP ----------------------------------------------------------------------------------- To create those type of functions in the DB2 database, open the Command Editor, specify an “@” as the statement delimiter for SQL statements, (you can specify the character to be used as the termination character for your statements and commands in the Statement termination character field), open the file create_udfs_functions.db2, and execute it by pressing Ctrl-Enter. To delete the given function, open the Command Editor, open the file drop_udfs_functions.db2, and execute it by pressing Ctrl-Enter. ----------------------------------------------------------------------------------- I am pleased to offer, free of charge, the following string functions SQL Procedural Language DB2 : 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 SQL Procedural Language DB2 function LOCATE, 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 SQL Procedural Language DB2 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 LOCATE, 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 SQL Procedural Language DB2 : 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 SQL Procedural Language DB2 LOCATE, 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 varchar(4000) Specifies the character expression that AT( ) searches for in cExpressionSearched. cExpressionSearched varchar(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 VALUES VFP.AT( 'is the', 'Now is the time for all good men'); -- Displays 5 VALUES VFP.AT( 'oo', 'goood men', 2); -- Displays 3, including overlaps 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 varchar(4000) Specifies the character expression that RAT( ) looks for in cExpressionSearched. cExpressionSearched varchar(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 VALUES VFP.RAT( 'a' , 'abracadabra', 3); -- Displays 6 VALUES VFP.RAT( 'oo', 'goood men', 2); -- Displays 2, including overlaps 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 varchar(4000) Specifies a character expression that OCCURS() searches for within cExpressionSearched. cExpressionSearched varchar(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 VALUES VFP.OCCURS('a', 'abracadabra'); -- Displays 5 VALUES VFP.OCCURS('b', 'abracadabra'); -- Displays 2 Attention, including overlaps !!! VALUES VFP.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 Returns the number of times a character expression occurs within another character expression (excluding overlaps). OCCURS2( cSearchExpression, cExpressionSearched) Return Values smallint Parameters cSearchExpression varchar(4000) Specifies a character expression that OCCURS2() searches for within cExpressionSearched. cExpressionSearched varchar(4000) Specifies the character expression OCCURS2() searches for cSearchExpression. Remarks OCCURS2() returns 0 (zero) if cSearchExpression is not found within cExpressionSearched. Example 1 VALUES VFP.OCCURS2('a', 'abracadabra'); -- Displays 5 Example 2 Counts the occurrences of differents characters from a string gcCaracters in string gcString VALUES VFP.OCCURS2('a', 'abracadabra') ;-- Displays 5 Example 2 Counts the occurrences of differents characters from a string gcCaracters in string gcString BEGIN ATOMIC DECLARE gcString varchar(4000); DECLARE gcCaracters varchar(256); DECLARE i , counter smallint; SET i = 1, counter = 0; SET gcString = '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 = 'abcca'; WHILE i <= length( gcCaracters) DO IF LOCATE(SUBSTR ( gcCaracters, i,1), LEFT( gcCaracters, i - 1)) = 0 THEN SET counter = counter + VFP.OCCURS2(SUBSTR( gcCaracters, i,1), gcString) ; END IF; SET i = i + 1; END WHILE; END @ Attention !!! This function counts the 'occurs' excluding overlaps ! VALUES VFP.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 varchar(4000) Parameters eExpression varchar(4000) Specifies the expression to be padded. nResultSize smallint Specifies the total number of characters in the expression after it is padded. cPadCharacter varchar(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 VALUES VFP.PADL( 'SQL PL DB2', 40); VALUES VFP.PADL( 'SQL PL DB2', 40, '=!='); VALUES VFP.PADR( 'SQL PL DB2', 40, '=+='); VALUES VFP.PADC( 'SQL PL DB2', 40, '=~'); 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 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 VALUES VFP.CHRTRAN('ABCDEF', 'ACE', 'XYZ'); -- Displays XBYDZF VALUES VFP.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(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 VALUES VFP.STRTRAN('ABCDEF', 'ABC', 'XYZ',-1,-1,0); -- Displays XYZDEF VALUES VFP.STRTRAN('ABCDEF', 'ABC', '',-1,-1,0); -- Displays DEF VALUES VFP.STRTRAN('ABCDEFABCGHJabcQWE', 'ABC', '',2,-1,0); -- Displays ABCDEFGHJabcQWE VALUES VFP.STRTRAN('ABCDEFABCGHJabcQWE', 'ABC', '',2,-1,1); -- Displays ABCDEFGHJQWE VALUES VFP.STRTRAN('ABCDEFABCGHJabcQWE', 'ABC', 'XYZ', 2, 1, 1); -- Displays ABCDEFXYZGHJabcQWE VALUES VFP.STRTRAN('ABCDEFABCGHJabcQWE', 'ABC', 'XYZ', 2, 3, 1); -- Displays ABCDEFXYZGHJXYZQWE VALUES VFP.STRTRAN('ABCDEFABCGHJabcQWE', 'ABC', 'XYZ', 2, 1, 2); -- Displays ABCDEFXYZGHJabcQWE VALUES VFP.STRTRAN('ABCDEFABCGHJabcQWE', 'ABC', 'XYZ', 2, 3, 2); -- Displays ABCDEFXYZGHJabcQWE VALUES VFP.STRTRAN('ABCDEFABCGHJabcQWE', 'ABC', 'xyZ', 2, 1, 2); -- Displays ABCDEFXYZGHJabcQWE VALUES VFP.STRTRAN('ABCDEFABCGHJabcQWE', 'ABC', 'xYz', 2, 3, 2); -- Displays ABCDEFXYZGHJabcQWE VALUES VFP.STRTRAN('ABCDEFAbcCGHJAbcQWE', 'Aab', 'xyZ', 2, 1, 2); -- Displays ABCDEFAbcCGHJAbcQWE VALUES VFP.STRTRAN('abcDEFabcGHJabcQWE', 'abc', 'xYz', 2, 3, 2); -- Displays abcDEFxyzGHJxyzQWE VALUES VFP.STRTRAN('ABCDEFAbcCGHJAbcQWE', 'Aab', 'xyZ', 2, 1, 3); -- Displays ABCDEFAbcCGHJAbcQWE VALUES VFP.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 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 VALUES VFP.STRFILTER('asdfghh5hh1jk6f3b7mn8m3m0m6','0123456789'); -- Displays 516378306 VALUES VFP.STRFILTER('ABCDABCDABCD', 'AB'); -- Displays ABABAB See Also CHRTRAN() GETWORDCOUNT() User-Defined Function Counts the words in a string. GETWORDCOUNT( cString[, cDelimiters]) Return Value smallint Parameters cString varchar(4000) - 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 VFP.GETWORDCOUNT(), you can get all the following results. VALUES VFP.GETWORDCOUNT( 'AAA aaa, BBB bbb, CCC ccc.'); -- 6 - character groups, delimited by ' ' VALUES VFP.GETWORDCOUNT( 'AAA aaa, BBB bbb, CCC ccc.', ','); -- 3 - 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 varchar(4000) Parameters cString varchar(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 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 VALUES VFP.GETWORDNUM('To be, or not to be: that is the question:', 10, ' ,.:');-- Displays 'question' See Also GETWORDCOUNT() User-Defined Function GETALLWORDS() User-Defined Function Inserts the words from a string into the table. GETALLWORDS( cString[, cDelimiters]) Return Value table GETALLWORDS (WORDNUM smallint, WORD varchar(4000), STARTOFWORD smallint, LENGTHOFWORD smallint) Parameters cString varchar(4000) - 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 function ignores spaces and tabs and uses only the specified character. Example SELECT * FROM TABLE(VFP.GETALLWORDS('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.')) AS A; 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 varchar(4000), STARTOFWORD smallint, LENGTHOFWORD smallint) Parameters cString varchar(4000) - Specifies the string whose words will be inserted into the table GETALLWORDS2. cStringSplitting varchar(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 SELECT WORD FROM TABLE(VFP.GETALLWORDS2('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.')) AS A where WORDNUM = 30 ; -- Displays 'Liberty' SELECT WORDNUM FROM TABLE(VFP.GETALLWORDS2('SQL Procedural Language DB2')) AS A ORDER BY WORDNUM DESC FETCH FIRST 1 ROW ONLY; -- Displays 4 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. 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(4000) Parameters cExpression varchar(4000) Specifies the character expression from which PROPER( ) returns a capitalized character string. Example VALUES VFP.PROPER( 'Visual Basic.NET'); -- Displays 'Visual Basic.net' VALUES VFP.PROPER( 'VISUAL BASIC.NET'); -- 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 VALUES VFP.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 VALUES VFP.ROMANTOARAB('MDCCCLXXXVIII'); -- Displays 1888 ----------------------------------------------------------------------------------- User-Defined Functions SQL PL DB2 , 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 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
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