How to Use the LIKE and EXCEPT Clauses

MORE INFORMATION

You can use the following commands to create a database so that you can test the examples listed below:

   CREATE TABLE test ;
   (afld  C(5), ;
   bfld  C(5), ;
   cfld  C(5), ;
   dfld1 C(5), ;
   dfld2 C(5), ;
   xfyd  C(5))

   INSERT INTO test ;
   (afld,  ;
   bfld,  ;
   cfld,  ;
   dfld1, ;
   dfld2, ;
   xfyd ) ;
   VALUES ;
   ('afld',  ;
   'bfld',  ;
   'cfld',  ;
   'dfld1', ;
   'dfld2', ;
   'xfyd' )
				

When you are using a LIKE or EXCEPT clause, you can specify a list of fields. For example, using the table created above, the following commands would set fields to "afld" and "bfld":

   SET FIELDS TO ALL LIKE a*, b*
   LIST
				

The following commands, demonstrating the EXCEPT clause, would set fields to "bfld" and "cfld":

   SET FIELDS TO ALL EXCEPT d*, a*
   LIST
				

You can also use LIKE and EXCEPT together. For example, the following commands would set fields to only "dfld1":

   SET FIELDS TO ALL LIKE d* EXCEPT dfld2
   LIST
				

The above statement would include all fields beginning with "d" (dfld1 and dfld2), but would then exclude all fields named "dfld2", which leaves "dfld1". You cannot specify a traditional field list when using LIKE or EXCEPT. For example, the following commands would set fields to "afld" and "bfld" but not "xfyd":

   SET FIELDS TO afld,bfld ALL LIKE x*
				

Only one LIKE clause and/or EXCEPT clause can be specified. A second instance of these clauses will be ignored. For example, the following statement will set fields to "dfld1". The "LIKE x*" portion of the command is ignored.

   SET FIELDS TO ALL LIKE d* EXCEPT dfld2* LIKE x*
				

You can also specify all fields that contain the specified text. For example, this command would set fields to "xfyd":

   SET FIELDS TO ALL LIKE *y*
				

Of those commands that accept a field list, the commands that now accept LIKE and EXCEPT clauses include: Of those commands that accept a field list, the commands that do not accept the LIKE/EXCEPT clauses include: