Special considerations for LIKE

 

Here are some considerations for using the LIKE predicate.

• When host variables are used in place of string constants in a search pattern, you should consider using varying length host variables. This allows you to:

  • Assign previously used string constants to host variables without any changes.

  • Obtain the same selection criteria and results as if a string constant were used.

• When fixed-length host variables are used in place of string constants in a search pattern, you should ensure that the value specified in the host variable matches the pattern previously used by the string constants. All characters in a host variable that are not assigned a value are initialized with a blank.

  For example, if you do a search using the string pattern 'ABC%' in a varying length host variable, these are some of the values that can be returned:

  'ABCD      '  'ABCDE'    'ABCxxx'      'ABC '

  However, if you do a search using the search pattern 'ABC%' contained in a host variable with a fixed length of 10, these values can be returned, assuming that the column has a length of 12:

  'ABCDE       ' 'ABCD        ' 'ABCxxx      ' 'ABC         '

  All returned values start with 'ABC' and end with at least 6 blanks. Blanks are used because the last 6 characters in the host variable are not assigned a specific value.

  If you want to do a search using a fixed-length host variable where the last 7 characters can be anything, search for 'ABC%%%%%%%'. These are some of the values that can be returned:

  'ABCDEFGHIJ'   'ABCXXXXXXX'  'ABCDE'      'ABCDD'

 

Parent topic:

Defining complex search conditions