Partager via

SELECT - SQL Command

  • Article

Retrieves data from one or more tables.

SELECT [ALL | DISTINCT] [TOP nExpr [PERCENT]] [Alias.] Select_Item 
   [[AS] Column_Name] [, [Alias.] Select_Item [[AS] Column_Name] ...] 
   FROM [FORCE][DatabaseName!]Table [[AS] Local_Alias] 
   [[INNER | LEFT [OUTER] | RIGHT [OUTER] | FULL [OUTER] JOIN 
      DatabaseName!] Table [[AS] Local_Alias] [ON JoinCondition ]
      [[INTO Destination] | [TO FILE FileName [ADDITIVE] | TO PRINTER 
      [PROMPT] | TO SCREEN]] [PREFERENCE PreferenceName]
      [NOCONSOLE] [PLAIN] [NOWAIT]
   [WHERE JoinCondition [AND JoinCondition ...]
      [AND | OR FilterCondition [AND | OR FilterCondition ...]]]
   [GROUP BY GroupColumn [, GroupColumn ...]] 
   [HAVING FilterCondition] [UNION [ALL] SELECTCommand] 
   [ORDER BY Order_Item [ASC | DESC] [, Order_Item [ASC | DESC] ...]]

Parameters

  • SELECT
    Specifies the fields, constants, and expressions that are displayed in the query results.

  • ALL
    By default, displays all the rows in the query results.

  • DISTINCT
    Excludes duplicates of any rows from the query results.

    Note   You can use DISTINCT only once per SELECT clause.

  • TOP nExpr [PERCENT]
    Specifies that the query result contains a specific number of rows or a percentage of rows in the query result. You must include an ORDER BY clause when you include the TOP clause. The ORDER BY clause specifies the columns on which the TOP clause determines the number of rows to include in the query result.

    You can specify from 1 to 32,767 rows. Rows with identical values for the columns specified in the ORDER BY clause are included in the query result. Therefore, if you specify 10 for nExpr, the query result can contain more than 10 rows if there are more than 10 rows with identical values for the columns specified in the ORDER BY clause.

    If the PERCENT keyword is included, the number of rows returned in the result is rounded up to the next highest integer. Permissible values for nExpr when the PERCENT keyword is included are 0.01 to 99.99.

  • Alias.
    Qualifies matching item names. Each item you specify with Select_Item generates one column of the query results. If two or more items have the same name, include the table alias and a period before the item name to prevent columns from being duplicated.

    Select_Item specifies an item to be included in the query results. An item can be one of the following:

    • The name of a field from a table in the FROM clause.
    • A constant specifying that the same constant value is to appear in every row of the query results.
    • An expression that can be the name of a user-defined function.

  • AS Column_Name
    Specifies the heading for a column in the query output. This option is useful when Select_Item is an expression or contains a field function and you want to give the column a meaningful name. Column_Name can be an expression but cannot contain characters (for example, spaces) that aren't permitted in table field names.

  • FROM
    Lists the tables containing the data that the query retrieves. If no table is open, Visual FoxPro displays the Open dialog box so you can specify the file location. Once open, the table remains open once the query is complete.

    FORCE specifies that tables are joined in the order in which they appear in the FROM clause. If FORCE is omitted, Visual FoxPro attempts to optimize the query. However, the query might be executed faster by including the FORCE keyword to disable the Visual FoxPro query optimization.

  • DatabaseName!
    Specifies the name of a non-current database containing the table. You must include the name of database containing the table if the database is not the current database. Include the exclamation point (!) delimiter after the database name and before the table name.

  • [AS] Local_Alias
    Specifies a temporary name for the table named in Table. If you specify a local alias, you must use the local alias in place of the table name throughout the SELECT statement.

    INNER JOIN specifies that the query result contains only rows from a table that match one or more rows in another table.

    LEFT [OUTER] JOIN specifies that the query result contains all rows from the table to the left of the JOIN keyword and only matching rows from the table to the right of the JOIN keyword. The OUTER keyword is optional; it can be included to emphasize that an outer join is created.

    RIGHT [OUTER] JOIN specifies that the query result contains all rows from the table to the right of the JOIN keyword and only matching rows from the table to the left of the JOIN keyword. The OUTER keyword is optional; it can be included to emphasize that an outer join is created.

    FULL [OUTER] JOIN specifies that the query result contains all matching and non matching rows from both tables. The OUTER keyword is optional; it can be included to emphasize that an outer join is created.

    ON JoinCondition specifies the conditions for which the tables are joined.

  • INTO Destination
    Specifies where to store the query results. If you include the INTO clause and the TO clause in the same query, the TO clause is ignored. If you don't include the INTO clause, query results are displayed in a Browse window. You can also use TO to direct query results to the printer or a file.

    Destination can be one of the following clauses:

    • ARRAY ArrayName, which stores query results in a memory variable array. The array isn't created if the query selects 0 records.

    • CURSOR CursorName [NOFILTER | READWRITE], which stores query results in a cursor. If you specify the name of an open table, Visual FoxPro generates an error message. After SELECT is executed, the temporary cursor remains open and is active and is read-only unless the READWRITE option was specified. When you close this temporary cursor, it is deleted. Cursors can exist as a temporary file on the drive or volume specified by SORTWORK.

      Include NOFILTER to create a cursor that can be used in subsequent queries. In previous versions of Visual FoxPro, it was necessary to include an extra constant or expression as a filter to create a cursor that could be used in subsequent queries. For example, adding a logical true as a filter expression created a query that could be used in subsequent queries:

      SELECT *, .T. FROM customers INTO CURSOR myquery

      Including NOFILTER can reduce query performance because a temporary table is created on disk. The temporary table is deleted from disk when the cursor is closed.

      The READWRITE clause specifies that the cursor is temporary and modifiable.

    • DBF | TABLE TableName[DATABASE DatabaseName [NAME LongTableName]] stores query results in a table. If you specify a table that is already open and SET SAFETY is set to OFF, Visual FoxPro overwrites the table without warning. If you don't specify an extension, Visual FoxPro gives the table a .DBF extension. The table remains open and active after SELECT is executed.

      Include DATABASE DatabaseName to specify a database to which the table is added. Include NAME LongTableName to specify a long name for the table. Long names can contain up to 128 characters and can be used in place of short file names in the database.

  • TO FILE FileName
    If you include a TO clause but not an INTO clause, you can direct query results to an ASCII text file named FileName, to the printer, or to the main Visual FoxPro window.

    ADDITIVE appends query output to the existing contents of the text file specified in TO FILE FileName.

    TO PRINTER [PROMPT] directs query output to a printer. Use the optional PROMPT clause to display a dialog box before printing starts. In this dialog box, you can adjust printer settings. The printer settings that you can adjust depend on the currently installed printer driver. Place PROMPT immediately after TO PRINTER.

    TO SCREEN directs query output to the main Visual FoxPro window or to an active user-defined window.

  • PREFERENCE PreferenceName
    Saves the Browse window's attributes and options for later use, if query results are sent to a Browse window. PREFERENCE saves the attributes, or preferences, indefinitely in the FOXUSER resource file. Preferences can be retrieved at any time.

    Issuing SELECT with a PREFERENCE PreferenceName for the first time creates the preference. Issuing SELECT later with the same preference name restores the Browse window to that preference state. When the Browse window is closed, the preference is updated.

    If you exit a Browse window by pressing CTRL+Q+W, changes you've made to the Browse window are not saved to the resource file.

  • NOCONSOLE
    Prevents display of query results sent to a file, the printer, or the main Visual FoxPro window.

  • PLAIN
    Prevents column headings from appearing in the query output that is displayed. You can use PLAIN whether or not a TO clause is present. If an INTO clause is included, PLAIN is ignored.

  • NOWAIT
    Continues program execution after the Browse window is opened and query results are directed to it. The program doesn't wait for the Browse window to be closed, but continues execution on the program line immediately following the SELECT statement.

    When TO SCREEN is included to direct output to the main Visual FoxPro window or to a user-defined window, output pauses when the main Visual FoxPro window or user-defined window is full of query results. Press a key to see the next set of query results. If NOWAIT is included, the query results are scrolled off the main Visual FoxPro window or the user-defined window without pausing for a key press. NOWAIT is ignored if included with the INTO clause.

  • WHERE
    Specifies that Visual FoxPro include only records in the query results that meet specified criteria.

  • JoinCondition
    Specifies fields that link the tables in the FROM clause. If you include more than one table in a query, you should specify a join condition for every table after the first.

    You must use the AND operator to connect multiple join conditions. Each join condition has the following form:

    FieldName1ComparisonFieldName2

    FieldName1 is the name of a field from one table, FieldName2 is the name of a field from another table, and Comparison is one of the following operators:

    Operator Comparison
    = Equal
    == Exactly equal
    LIKE SQL LIKE
    <>, !=, # Not equal
    > More than
    >= More than or equal to
    < Less than
    <= Less than or equal to

    When you use the = operator with strings, it acts differently depending on the setting of SET ANSI. When SET ANSI is set to OFF, Visual FoxPro treats string comparisons in a manner familiar to Xbase users. When SET ANSI is set to ON, Visual FoxPro follows ANSI standards for string comparisons. See SET ANSI and SET EXACT for additional information about how Visual FoxPro performs string comparisons.

    The WHERE clause supports the ESCAPE operator for the JoinCondition, making it possible for you to perform meaningful queries on data containing the SELECT - SQL % and _ wildcard characters.

    The ESCAPE clause makes it possible for you to specify that a SELECT - SQL wildcard character be treated as a literal character. In the ESCAPE clause you specify a character, which when placed immediately before the wildcard character, indicates that the wildcard character be treated as a literal character.

  • FilterCondition
    Specifies the criteria that records must meet to be included in the query results. You can include as many filter conditions as you like in a query, connecting them with the AND or OR operator. You can also use the NOT operator to reverse the value of a logical expression, or use EMPTY( ) to check for an empty field.

FilterCondition can take any of the forms in the following examples:

  • Example 1

Example 1 displays FilterCondition in the form of FieldName1ComparisonFieldName2

customer.cust_id = orders.cust_id
  • Example 2

Example 2 displays FilterCondition in the form of FieldNameComparisonExpression

payments.amount >= 1000
  • Example 3

Example 3 displays FilterCondition in the form of FieldNameComparison ALL (Subquery)

When the filter condition includes ALL, the field must meet the comparison condition for all values generated by the subquery before its record is included in the query results.

company < ALL ;
(SELECT company FROM customer WHERE country = "UK")
  • Example 4

Example 4 displays FilterCondition in the form of FieldNameComparison ANY | SOME (Subquery)

When the filter condition includes ANY or SOME, the field must meet the comparison condition for at least one of the values generated by the subquery.

company < ANY ;
(SELECT company FROM customer WHERE country = "UK")
  • Example 5

Example 5 displays FilterCondition in the form of FieldName [NOT] BETWEEN Start_Range AND End_Range

This example checks to see whether the values in the field are within a specified range of values.

customer.postalcode BETWEEN 90000 AND 99999
  • Example 6

Example 6 displays FilterCondition in the form of [NOT] EXISTS (Subquery)

This example checks to see whether at least one row meets the criterion in the subquery. When the filter condition includes EXISTS, the filter condition evaluates to true (.T.) unless the subquery evaluates to the empty set.

EXISTS ; 
(SELECT * FROM orders WHERE customer.postalcode =    orders.postalcode)
  • Example 7

Example 7 displays FilterCondition in the form of FieldName [NOT] IN Value_Set

When the filter condition includes IN, the field must contain one of the values before its record is included in the query results.

customer.postalcode NOT IN ("98052","98072","98034")
  • Example 8

Example 8 displays FilterCondition in the form of FieldName [NOT] IN (Subquery)

Here, the field must contain one of the values returned by the subquery before its record is included in the query results.

customer.cust_id IN ; 
(SELECT orders.cust_id FROM orders WHERE orders.city="Seattle")
  • Example 9

Example 9 displays FilterCondition in the form of FieldName [NOT] LIKE cExpression

  • customer.country NOT LIKE "UK"

  • This filter condition searches for each field that matches cExpression.

You can use the percent sign (%) and underscore ( _ ) wildcards as part of cExpression. The percent sign represents any sequence of unknown characters in the string. An underscore represents a single unknown character in the string.

  • GROUP BY GroupColumn [, GroupColumn ...]
    Groups rows in the query based on values in one or more columns. GroupColumn can be the name of a regular table field, a field that includes a SQL field function, or a numeric expression indicating the location of the column in the result table (the leftmost column number is 1).

  • HAVING FilterCondition
    Specifies a filter condition, which groups must meet to be included in the query results. HAVING should be used with GROUP BY. It can include as many filter conditions as you like, connected with the AND or OR operators. You can also use NOT to reverse the value of a logical expression.

    FilterCondition cannot contain a subquery.

    A HAVING clause without a GROUP BY clause acts like a WHERE clause. You can use local aliases and field functions in the HAVING clause. Use a WHERE clause for faster performance if your HAVING clause contains no field functions. Note that the HAVING clause should appear before an INTO clause or a syntax error occurs.

  • [UNION [ALL] SELECTCommand]
    Combines the final results of one SELECT with the final results of another SELECT. By default, UNION checks the combined results and eliminates duplicate rows. Use parentheses to combine multiple UNION clauses.

    ALL prevents UNION from eliminating duplicate rows from the combined results.

    UNION clauses follow these rules:

    • You cannot use UNION to combine subqueries.
    • Both SELECT commands must have the same number of columns in their query output.
    • Each column in the query results of one SELECT must have the same data type and width as the corresponding column in the other SELECT.
    • Only the final SELECT can have an ORDER BY clause, which must refer to output columns by number. If an ORDER BY clause is included, it affects the entire result.

    You can also use the UNION clause to simulate an outer join.

    When you join two tables in a query, only records with matching values in the joining fields are included in the output. If a record in the parent table doesn't have a corresponding record in the child table, the record in the parent table isn't included in the output. An outer join allows you to include all the records in the parent table in the output, along with the matching records in the child table. To create an outer join in Visual FoxPro, you need to use a nested SELECT command, as in the following example:

    SELECT customer.company, orders.order_id, orders.emp_id ;
   FROM customer, orders ;
   WHERE customer.cust_id = orders.cust_id ;
UNION ;
   SELECT customer.company, 0, 0 ;
   FROM customer ;
   WHERE customer.cust_id NOT IN ;
   (SELECT orders.cust_id FROM orders)

    **Note   **Be sure to include the space immediately preceding each semicolon. Otherwise, you'll receive an error.

    The section of the command before the UNION clause selects records from both tables that have matching values. The customer companies that do not have associated invoices are not included. The section of the command after the UNION clause selects records in the customer table that do not have matching records in the orders table.

    Regarding the second section of the command, note the following:

    • The SELECT statement within the parentheses is processed first. This statement results in a selection of all customer numbers in the orders table.

    • The WHERE clause finds all customer numbers in the customer table that are not in the orders table. Since the first section of the command provided all companies that had a customer number in the orders table, all companies in the customer table are now included in the query results.

    • Because the structures of tables included in a UNION must be identical, there are two placeholders in the second SELECT statement to represent orders.order_id and orders.emp_id from the first SELECT statement.

      **Note   **The placeholders must be the same type as the fields they represent. If the field is a date type, the placeholder should be { / / }. If the field is a character field, the placeholder should be the empty string ("").

      Under certain circumstances automatic data transformation may take place, as in the following two examples:

      CREATE CURSOR table1 (field1 c(1))
CREATE CURSOR table2 (field1 c(2))
SELECT field1 FROM table1 UNION SELECT NVL(field1,'  ') FROM table2  

CREATE CURSOR table1 (field1 n(20,5))
CREATE CURSOR table2 (field1 i)
SELECT field1 FROM table1 UNION SELECT field1 FROM table2

  • ORDER BY Order_Item
    Sorts the query results based on the data in one or more columns. Each Order_Item must correspond to a column in the query results and can be one of the following:

    • A field in a FROM table that is also a select item in the main SELECT clause (not in a subquery).
    • A numeric expression indicating the location of the column in the result table. (The leftmost column is number 1.)

    ASC specifies an ascending order for query results, according to the order item or items, and is the default for ORDER BY.

    DESC specifies a descending order for query results.

    Query results appear unordered if you don't specify an order with ORDER BY.

Remarks

SELECT is a SQL command that is built into Visual FoxPro like any other Visual FoxPro command. When you use SELECT to pose a query, Visual FoxPro interprets the query and retrieves the specified data from the tables. You can create a SELECT query from within:

  • The Command window
  • A Visual FoxPro program (as with any other Visual FoxPro command)
  • The Query Designer

When you issue SET TALK ON and execute SELECT, Visual FoxPro displays the length of time the query took to execute and the number of records in the results. _TALLY contains the number of records in the query results.

SELECT does not respect the current filter condition specified with SET FILTER.

Note   A subquery, referred to in the following arguments, is a SELECT within a SELECT and must be enclosed in parentheses. You can have up to two subqueries at the same level (not nested) in the WHERE clause (see that section of the arguments). Subqueries can contain multiple join conditions.

When you create query output, columns are named according to the following rules:

  • If a select item is a field with a unique name, the output column name is the field's name.
  • If more than one select item has the same name, an underscore and a letter are appended to the column name. For example, if a table called Customer has a field called STREET, and a table called Employees also has a field called STREET, output columns are named Extension_A and Extension_B (STREET_A and STREET_B). For a select item with a 10-character name, the name is truncated to add the underscore and letter. For example, DEPARTMENT would become DEPARTME_A.
  • If a select item is an expression, its output column is named EXP_A. Any other expressions are named EXP_B, EXP_C, and so on.
  • If a select item contains a field function such as COUNT( ), the output column is named CNT_A. If another select item contains SUM( ), its output column is named SUM_B.

User-Defined Functions with SELECT   Although using user-defined functions in the SELECT clause has obvious benefits, you should also consider the following restrictions:

  • The speed of operations performed with SELECT may be limited by the speed at which such user-defined functions are executed. High-volume manipulations involving user-defined functions may be better accomplished by using API and user-defined functions written in C or assembly language.
  • You can assume nothing about the Visual FoxPro input/output (I/O) or table environment in user-defined functions invoked from SELECT. In general, you don't know which work area is selected, the name of the current table, or even the names of the fields being processed. The value of these variables depends on where precisely in the optimization process the user-defined function is invoked.
  • It isn't safe to change the Visual FoxPro I/O or table environment in user-defined functions invoked from SELECT. In general, the results are unpredictable.
  • The only reliable way to pass values to user-defined functions invoked from SELECT is by the argument list passed to the function when it is invoked.
  • If you experiment and discover a supposedly forbidden manipulation that works correctly in a certain version of FoxPro, there is no guarantee it will continue to work in later versions.

Apart from these restrictions, user-defined functions are acceptable in the SELECT clause. However, don't forget that using SELECT might slow performance.

The following field functions are available for use with a select item that is a field or an expression involving a field:

  • AVG(Select_Item), which averages a column of numeric data.
  • COUNT(Select_Item), which counts the number of select items in a column. COUNT(*) counts the number of rows in the query output.
  • MIN(Select_Item), which determines the smallest value of Select_Item in a column.
  • MAX(Select_Item), which determines the largest value of Select_Item in a column.
  • SUM(Select_Item), which totals a column of numeric data.

You cannot nest field functions.

Joins   Visual FoxPro supports ANSI SQL '92 Join syntax, making it possible for you to create queries that link the rows in two or more tables by comparing the values in specified fields. For example, an inner join selects rows from two tables only when the values of the joined fields are equal. Visual FoxPro supports nested joins.

Because SQL is based on mathematical set theory, each table can be represented as a circle. The ON clause that specifies the join conditions determines the point of overlap, which represents the set of rows that match. For an inner join, the overlap occurs within the interior or "inner" portion of the two circles. An outer join includes not only those matched rows found in the inner cross section of the tables, but also the rows in the outer part of the circle to the left, or right, of the intersection.

Note   Keep the following information in mind when creating join conditions:

  • If you include two tables in a query and don't specify a join condition, every record in the first table is joined with every record in the second table as long as the filter conditions are met. Such a query can produce lengthy results.
  • Be careful when using, in join conditions, functions such as DELETED( ), EOF( ), FOUND( ), RECCOUNT( ), and RECNO( ), which support an optional alias or work area. Including an alias or work area in these functions might yield unexpected results. SELECT doesn't use your work areas; it performs the equivalent of USE ... AGAIN. Single-table queries that use these functions without an optional alias or work area will return proper results. However, multiple-table queries that use these functions — even without an optional alias or work area — might return unexpected results.
  • Use caution when joining tables that contain empty fields because Visual FoxPro matches empty fields. For example, if you join on CUSTOMER.ZIP and INVOICE.ZIP, and CUSTOMER contains 100 empty zip codes and INVOICE contains 400 empty zip codes, the query output contains 40,000 extra records resulting from the empty fields. Use the EMPTY( ) function to eliminate empty records from the query output.
  • There are limits to the number joins you can use. The maximum number of joins per Select statement is nine.

Including the EVALUATE( ) function in the WHERE clause of a SQL query can return incorrect data.

For additional information about joins, see Defining Multiple Join Conditions under Creating Views.

Examples

The following examples illustrate the use of user-defined functions with SELECT - SQL:

Example 1

Example 1 displays the names of all companies in customer (one field from one table).

CLOSE ALL
CLOSE DATABASES
OPEN DATABASE (HOME(2) + 'data\testdata')
SELECT customer.company ;
   FROM customer

Example 2

Example 2 displays the contents of three fields from two tables and joins the two tables based on the cust_id field. It uses local aliases for both tables.

CLOSE ALL
CLOSE DATABASES
OPEN DATABASE (HOME(2) + 'data\testdata')
SELECT x.company, y.order_date, y.shipped_on ;
   FROM customer x, orders y ;
   WHERE x.cust_id = y.cust_id

Example 3

Example 3 displays only records with unique data in the specified fields.

CLOSE ALL
CLOSE DATABASES
OPEN DATABASE (HOME(2) + 'data\testdata')
SELECT DISTINCT x.company, y.order_date, y.shipped_on ;
   FROM customer x, orders y ;
   WHERE  x.cust_id = y.cust_id

Example 4

Example 4 displays the country, postalcode, and company fields in ascending order.

CLOSE ALL
CLOSE DATABASES
OPEN DATABASE (HOME(2) + 'data\testdata')
SELECT country, postalcode, company ;
   FROM customer ;
   ORDER BY country, postalcode, company

Example 5

Example 5 stores the contents of fields from two tables in a third table.

CLOSE ALL
CLOSE DATABASES
OPEN DATABASE (HOME(2) + 'data\testdata')
SELECT x.company, y.order_date, y.shipped_on ;
   FROM customer x, orders y ;
   WHERE x.cust_id = y.cust_id ;
   INTO TABLE custship.dbf
BROWSE

Example 6

Example 6 displays only records with an order date earlier than 02/16/1994.

CLOSE ALL
CLOSE DATABASES
OPEN DATABASE (HOME(2) + 'data\testdata')
SELECT x.company, y.order_date, y.shipped_on ;
   FROM customer x, orders y ;
   WHERE x.cust_id = y.cust_id ;
   AND y.order_date < {^1994-02-16}

Example 7

Example 7 displays the names of all companies from customer with a postal code that matches a postal code in the orders table.

CLOSE ALL
CLOSE DATABASES
OPEN DATABASE (HOME(2) + 'data\testdata')
SELECT company FROM customer x WHERE ;
   EXISTS (SELECT * FROM orders y WHERE x.postalcode = y.postalcode)

Example 8

Example 8 displays all records from customer having a company name that begins with an uppercase C and is an unknown length.

CLOSE ALL
CLOSE DATABASES
OPEN DATABASE (HOME(2) + 'data\testdata')
SELECT * FROM customer x WHERE x.company LIKE "C%"

Example 9

Example 9 displays all records from customer having a country name that begins with an uppercase U and is followed by one unknown character.

CLOSE ALL
CLOSE DATABASES
OPEN DATABASE (HOME(2) + 'data\testdata')
SELECT * FROM customer x WHERE x.country LIKE "U_"

Example 10

Example 10 displays the names of all cities in customer in uppercase and names the output column CityList.

CLOSE ALL
CLOSE DATABASES
OPEN DATABASE (HOME(2) + 'data\testdata')
SELECT UPPER(city) AS CityList FROM customer

Example 11

Example 11 demonstrates how you can perform a query on data that contains percentage signs (%). A backslash (\) is placed before the percentage sign to indicate that it should be treated as a literal, and the backslash is specified as the escape character in the ESCAPE clause.

Because the sample tables included with Visual FoxPro do not contain the percentage sign character, this query returns no results.

CLOSE ALL
CLOSE DATABASES
OPEN DATABASE (HOME(2) + 'data\testdata')
SELECT * FROM customer;
WHERE company LIKE "%\%%" ESCAPE "\"

Example 12

Example 12 demonstrates how you can perform a query on data that contains underscores (_). A backslash (\) is placed before the underscore to indicate that it should be treated as a literal, and the backslash is specified as the escape character in the ESCAPE clause.

Because the sample tables included with Visual FoxPro do not contain the underscore character, this query returns no results.

CLOSE ALL
CLOSE DATABASES
OPEN DATABASE (HOME(2) + 'data\testdata')
SELECT * FROM customer;
WHERE company LIKE "%\_%" ESCAPE "\"

Example 13

In example 13, the Escape character uses itself as a literal. The dash is both the escape character and a literal. The query returns all rows where the company name contains a percentage sign followed by a dash.

Because the sample tables included with Visual FoxPro do not contain the percentage sign character, this query returns no results.

CLOSE ALL
CLOSE DATABASES
OPEN DATABASE (HOME(2) + 'data\testdata')
SELECT * FROM customer;
WHERE company LIKE "%-%--%" Escape "-"

See Also

CREATE QUERY | CREATE TABLE - SQL | INSERT - SQL | MODIFY QUERY | SET ANSI | SET EXACT | SET PATH | _TALLY | Defining Multiple Join Conditions