Microsoft KB Archive/98434

= How to use wildcard characters in the SQL-SELECT statement in Visual FoxPro =

Article ID: 98434

Article Last Modified on 2/12/2007

-

APPLIES TO


 * Microsoft Visual FoxPro 7.0 Professional Edition
 * Microsoft Visual FoxPro 8.0 Professional Edition
 * Microsoft Visual FoxPro 9.0 Professional Edition

-



This article was previously published under Q98434



SUMMARY
To broaden the selections of a structured query language (SQL-SELECT) statement, two wildcard characters, the percent sign (%) and the underscore (_), can be used.

The percent sign is analogous to the asterisk (*) wildcard character used with MS-DOS. The percent sign allows for the substitution of one or more characters in a field.

The underscore is similar to the MS-DOS wildcard question mark character. The underscore allows for the substitution of a single character in an expression. Three examples showing the use of these characters in SQL-SELECT statements are provided below. The examples use the Customer.dbf file that is included with Microsoft Visual FoxPro in the Home(2)+data folder.



MORE INFORMATION
The following SELECT-SQL statement returns all the records from the Customer.dbf table that contain a &quot;W&quot; as the first character in the Regionabbreviation field. The SELECT-SQL statement lists the records from &quot;WY&quot; and &quot;WA&quot; in a Browse window. SELECT * from customer WHERE Region like &quot;W_&quot; The following SELECT-SQL statement lists all the records that have a &quot;P&quot; or a &quot;C&quot; as the last character in the Regionabbreviation field. The output includes records from &quot;SP&quot; and &quot;BC.&quot; SELECT * from customer WHERE Region like &quot;_P&quot; OR region like &quot;_C&quot; The following SELECT-SQL statement uses the percent sign and the underscore to return all the records that have a Cust_ID that starts with &quot;G&quot; and that have a Regionabbreviation field that ends with &quot;P.&quot;

Note The percent sign lets any string of characters follow the &quot;G.&quot; Conversely, the underscore permits the substitution of only a single character. SELECT * FROM Customer WHERE cust_ID LIKE &quot;G%&quot; AND region LIKE &quot;_P&quot; To do a string search similar to the one performed by the $ function, use a statement like: SELECT * from customer WHERE company like &quot;%M%&quot; This SELECT-SQL statement finds all companies that have names that contain &quot;M.&quot;

