Visual FoxPro functions for processing text

From the start, FoxPro has always had a wide range of commands and functions for processing text - more than are strictly necessary for a database language. This is a list of some of the more useful commands and functions in Visual FoxPro. Many of them can accept extra optional parameters. Look in the FoxPro Help system for more information.

The distinction between commands and functions is that commands do something whereas functions just return a value to be displayed or used in another command.

FoxPro syntax requires that functions are always followed by a pair of brackets to hold the parameters - even if there's no parameter required. Unlike Visual Basic, FoxPro does not use the "$" suffix in functions to force them to return a string. VFP variables are all of the same type, something equivalent to a variant in VB.

<text1> + <text2> return <text1text2>
<text1> - <text2> return <text1text2> with any spaces from <text1> removed and added to the end of the result. An unusual function but sometimes useful.
<text1> $ <text2> return .T. if <text1> exists in <text2>
ALLTRIM(<text>) return <text> with all leading and trailing spaces removed. In VFP 9 this function can remove characters other than spaces by specifying an extra parameter.
AT(<char>,<text>) return the position of character <char> in string <text> or 0 if <char> is not found. The RAT() function does the same but starting at the end of the target string <text>.
ATC(<char>,<text>) this is identical to to the AT() function but it is not case-sensitive.
CTOD(<text>) return <text> as a variable in date format. Note that the CTOD function will fail if STRICTDATE is set to 2.
DTOC(<date>) return <date> as a string in 'dd/mm/yy' or 'dd/mm/yyyy' format depending on the state of SET CENTURY and SET DATE.
DTOS(<date>) return <date> as a variable of type string in 'yyyymmdd' format. Use the DTOS function when you are wanting to index on an expression like Surname + DTOS(DateOfBirth).
FILETOSTR(<filename>) return a string holding the contents of the file named <filename>. Useful in combination with STRTOFILE because you can read a file from disk into memory, process it very quickly, and then write it back out to disk. The only limitation in the 16Mb limit on FoxPro string variables.
ISALPHA(<text>) return .T. if the first character of <text> is alphabetic.
ISLOWER(<text>) return .T. if the first character of <text> is lower case.
ISUPPER(<text>) return .T. if the first character of <text> is upper case.
LEFT(<text>, <n>) return the leftmost <n> characters of <text>. Note that this will include any spaces.
LEN(<text>) return the length of <text> - including spaces. Remember this when you are getting the length of data retrieved from a field.
LIKE(<text1>, <text2>) compares <text1> and <text2> character by character and returns .T. if they match. <text1> can include the wild cards '*' and '?'.
LOWER(<text>) return <text> as text in lowercase.
LTRIM(<text>) return <text> with the leading spaces removed.
OCCURS(<text1>, <text2>) return the number of times that the <text1> occurs in <text2>.
PADC(<text>, <n>, <char>) pad <text> to <n> characters long by adding equal numbers of <char> to both ends.
PADL(<text>, <n>, <char>) pad <text> to <n> characters long by adding <char> to the start.
PADR(<text>, <n>, <char>) pad <text> to <n> characters long by adding <char> to the end.
PROPER(<text>) return <text> in lowercase text with the initial letters capitalised. Not as useful as you might think because many proper names include a mixture of uppercase and lowercase letters which must be retained - "McDonald" and "IBM" for example.
RIGHT(<text>, <n>) return the rightmost <n> characters of <text>. Remember that the count of <n> will include trailing spaces.
RTRIM(<text>) return <text> with the trailing spaces removed.
STR(<number>,<m>,<n>) return <number> as a string of <m> characters rounded to <n> decimals.
STREXTRACT(<text>, <begin>, <end>) returns the characters between the delimiters <begin> and <end> inside <text>. Additional parameters let you distinguish between multiple occurences of the delimiters and choose whether the delimiters are included in the text which is to be returned. The StrExtract function is very useful when processing HTML and XML.
STRTOFILE(<filename>) write a string to a file and return the number of bytes written. Useful in combination with FILETOSTR because you can read a file from disk into memory, process it, and write it back out to disk.
STRTRAN(<text>, <a>, <b>) replace every occurrence of <a> inside <text> with another character or characters <b>.
STUFF(<text>, <m>, <n>, <a>) replace <n> characters from position <m> in <text> with the character or characters <a>.
TRIM(<text>) identical to RTRIM.
SUBSTR(<text>,<m>,<n>) return <n> characters from within <text> starting at character <m>.
UPPER(<text>) return <text> in upper case text.

We have a page showing the comparison between VBA and VFP text functions.

Access Tips

FoxPro Tips

General Tips


Related Items

FoxPro Functions

FoxPro has always had functions like FREAD and FWRITE to read and write binary files at a low level. They can handle files which defeat the STRTOFILE and FILETOSTR functions.

Read More

Useful Visual FoxPro Commands

Description of useful Visual FoxPro commands and functions

Read More

American date formats used by Access SQL

Microsoft Access queries use an American date format in SQL commands.

Read More

Visual FoxPro functions for file names

FoxPro functions to manipulate names of files and folders

Read More

FoxPro functions for manipulating tables

FoxPro commands and functions to manipulate tables

Read More