Specialists in Microsoft Access and Visual Foxpro database training and development

I am happy to hear from existing clients but I am taking no new calls.

March 2015

I have now retired and am taking on no new work. I'd like to thank all our customers over the past 20 years. It really has been a most enjoyable time.

Visual FoxPro functions for processing text

FoxPro has always had a wide range of commands and functions for processing text -
probably more than were strictly necessary for a database language. This is a list of
some of the more useful commands and functions. Many of them can accept extra optional
parameters to refine their behaviour. Look in the FoxPro Help system for more information.

The distinction between VFP commands and functions is that commands
do something whereas functions just return a value to be displayed
or used in some other way.

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 have no strict type.

<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>

ALINES(<array> ,<text>)

return the number of lines in <text> and stores each line in
<array>. If the array does not exist then it is created. Optional
extra parameters define how the text is to be split into lines.

ALLTRIM(<text>)

return <text> with leading and trailing spaces removed. In
VFP 9 and above this can remove characters other than space.

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>)

identical to AT but is not case-sensitive.

CTOD(<text>)

return <text> as a variable of type date. Note that CTOD
will fail if STRICTDATE is set to 2.

DTOC(<date>)

return <date> as a variable of type 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 DTOS() 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, and then write it back
out to disk. The only limitation in the 16Mb limit on FoxPro
string variables.

GETWORDCOUNT(<text>)

return the number of words in <text>. Optional second parameter
sets the delimiter between words. The default is a space.

GETWORDNUM
(<text>,<n>)

return the nth word in <text>. Optional third parameter
sets the delimiter between words. The default is a space, tab, carriage
return or linefeed.

ISALPHA(<text>)

return .T. if the first character of <text> is a letter.

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>.

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> letter by letter and
returns .T. if they match. <text1> can include the wild
cards '*' and '?'.

LOWER(<text>)

return <text> as lowercase text.

LTRIM(<text>)

return <text> with leading spaces removed.

MLINE(<field>, <n>)

return the nth line from the memo field named <field>.

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
<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 like "McDonald" and "IBM".

RIGHT(<text>, <n>)

return the rightmost <n> characters of <text>.
Remember that this will include trailing spaces.

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
returned. 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.