English

How To: Compare a field with a substring in another field

Summary

Instructions provided describe how to use Select by Attributes to find all the features for which Field1 is contained in Field2.

Procedure

For example, select features for which Field1='Forest' and Field2='Pine Forest'. The syntax shown here is for shapefiles, but it can be adapted for any datasource which supports the SQL CONCAT, SUBSTRING and CHAR_LENGTH functions.

  1. Select 'Select by Attributes' from the Selection menu.
  2. Build an expression such as "Field2" LIKE '%[Field1 content]%'.
    Using "Field1" returns the content of Field1 in SQL format which is surrounded by single quotes. The expression needs to extract the literal value of Field1 using SUBSTRING and CHAR_LENGTH:

    SUBSTRING("Field1",1,CHAR_LENGTH("Field1"))
  3. Concatenate the string with % to use it with the LIKE operator:

    CONCAT(CONCAT('%',SUBSTRING("Field1",1,CHAR_LENGTH("Field1"))),'%')

    Note that CONCAT is used twice as it only concatenates two strings at a time.
  4. The final expression will be:

    "Field2" LIKE CONCAT(CONCAT('%',SUBSTRING("Field1",1,CHAR_LENGTH("Field1"))),'%')