Varchar Field Type

To include alphanumeric text in fields without including additional padding with spaces or truncating trailing spaces, use the Varchar type. Alphanumeric text can contain letters, numbers, spaces, symbols, and punctuation. The Varchar type also provides a convenient method for mapping SQL Server Varchar types to corresponding Visual FoxPro data types.

To prevent data in Varchar fields from being translated across code pages, use the Varchar (Binary) type.

The Varchar and Varchar (Binary) field types are similar to the Character data and Character (Binary) field type in that you can store text information that is not used in mathematical calculations, such as names, addresses, and numbers. When storing Varchar and Varchar (Binary) values in variables, they are treated as Character types.

Tip

To create a Varchar (Binary) field in a table, use the NOCPTRANS option in the SQL CREATE TABLE and CREATE CURSOR commands or select it in the Fields tab in the Table Designer.

The Varchar and Varchar (Binary) field types have priority over the Character type when performing UNION and concatenation operations between these types. For example, suppose you have Varchar (X) and Character (Y) fields with X representing the length of character value stored in the Varchar field and Y representing the length of the Character field. When performing concatenation operations, the result is a Varchar field with a character value length of X+Y.

When performing concatenation operations between Varchar (Binary) (X) and Character (Binary) (Y), the result is Varchar (Binary) (X+Y). For Visual FoxPro functions that return Character values, if at least one parameter is a Varchar or Varchar (Binary) field in a table or cursor, the return type is Varchar. For more information about data type conversion and precedence in UNION operations, see Considerations for SQL SELECT Statements.

Index keys based on Varchar and Varchar (Binary) fields or expressions are padded with spaces from the right of the value to the maximum length of the field. Therefore, LIKE clauses in SQL join and filter conditions with a Varchar or Varchar (Binary) field can be only partially Rushmore-optimized. For more information, see Using Rushmore Query Optimization to Speed Data Access.

Support for Varchar and Varchar (Binary) Types

The Varchar and Varchar (Binary) field types are supported for database containers (.dbc), free tables, cursors, and local and remote views. For example, you can select these types for a field on the Fields tab in the Table Designer. Tables can contain multiple Varchar and Varchar (Binary) fields. You can specify default and null values for Varchar and Varchar (Binary) fields. Varchar and Varchar (Binary) fields support field validation.

The following table lists language that contains functionality affected by the Varchar and Varchar (Binary) field types.

AFIELDS( ) Function	ALTER TABLE - SQL Command	APPEND FROM Command
COPY STRUCTURE EXTENDED Command	COPY TO Command	CREATE CURSOR - SQL Command
CREATE FROM Command	CREATE TABLE - SQL Command	CURSORGETPROP( ) Function
CursorSchema Property	CURSORSETPROP( ) Function	CURSORTOXML( ) Function
DataType Property	Format Property	MaxLength Property
InputMask Property	SET ENGINEBEHAVIOR Command	SET ANSI Command
SET EXACT Command	XMLTOCURSOR( ) Function

For more specifications about the Varchar and Varchar (Binary) types, see Visual FoxPro Data and Field Types.

The following example clears the main Visual FoxPro window using the CLEAR command and creates a cursor with two fields named myVarCharField with Varchar type and myCharField with Character type using the SQL CREATE CURSOR command. For each SQL INSERT statement, the INSERT command inserts a row into the cursor containing character expressions of increasing length into each field. BROWSE displays the cursor, and GO TOP positions the record pointer at the first record. The DO WHILE loop displays the number of characters in each field for each row using the LEN( ) function until the last record in the table is reached.

You can type the example in a program (.prg) file and run it from the Command window using the DO Command.

CLEAR
CREATE CURSOR myCursor (myVarCharField V(10), myCharField C(10))
INSERT INTO myCursor (myVarCharField, myCharField) VALUES ("a", "a")
INSERT INTO myCursor (myVarCharField, myCharField) VALUES ("aa", "aa")
INSERT INTO myCursor (myVarCharField, myCharField) VALUES ("aaa", "aaa")
INSERT INTO myCursor (myVarCharField, myCharField) VALUES ("aaaa", "aaaa")
BROWSE
GO TOP
DO WHILE !EOF()
   ? "# VarChar characters: ", LEN(myVarCharField), ; 
      ", # Char characters:      ", LEN(myCharField)
   ? 
   SKIP
ENDDO

For more information about the commands and functions used in this example, see CLEAR Commands, CREATE CURSOR - SQL Command, INSERT - SQL Command, BROWSE Command, GOGOTO Command, DO WHILE ... ENDDO Command, EOF( ) Function, and SKIP Command.

See Also

Tasks

How to: Choose Data Types

Reference

Fields Tab, Table Designer
DISPLAY STRUCTURE Command
TYPE( ) Function
VARTYPE( ) Function

Other Resources

Data and Field Types