SQL


SQL		Pattern Matching

Pattern Matching

Pattern matching applies to the use of the LIKE keyword in a WHERE clause. This feature enables the finding of text strings with particular characteristics such as all starting with the same character.

Patterns are described by the use of special characters together with ordinary characters which are to be matched in the string being searched.

Characters in a pattern are taken literally unless they are one of the special pattern matching characters described below. For example,

         WHERE  ADDRESS  LIKE  'Ave'

finds all values of ADDRESS containing the string "Ave". The string "Ave" may appear anywhere. This is different to the EQ relational operator, as in:

         WHERE  ADDRESS  EQ  'Ave'

This condition will only be true when ADDRESS is exactly equal to the string "Ave".

For example, to find the name of everyone whose first name starts with "B" and second name starts with "L":

SELECT ID  NAME  CURRPOS  -
FROM    EMPLOYEE   -
WHERE   NAME   LIKE '%B?*L'

searches for and finds all rows in the EMPLOYEE table in which NAME starts with the letter "B" followed by zero, one, or more intervening characters followed by the letter "L".

Trim and Upper

Pattern matching is affected by system parameters CMPTRIM and CMPUPPER. CMPTRIM causes trailing blanks of strings to be trimmed before they are compared. CMPUPPER maps all strings to upper case before the comparison takes place. Generally, when using the LIKE function, SET CMPTRIM and CLEAR CMPUPPER. By default, both parameters are SET.

Special Characters

The special characters are:

%

beginning of line $

end of line ?

match any single character [

start a character class -

range of characters ]

end a character class !

negate a character class *

closure, zero or more occurrences +

closure, one or more occurrences

Beginning of the Line %

The % character specifies searching for patterns at the beginning of a string variable.

To find all rows that have a string variable which begin with the word PROCESS, use:

         ... WHERE variable LIKE '%PROCESS'

This returns only those rows that begin with the string "PROCESS". It does not return rows containing "PROCESS" in the middle of the variable such as "END PROCESS" or "EXIT PROCESS".

End of the Line $

The $ character specifies searching for patterns at the end of a string variable.

To search for all records in which the NAME column ends in "smith" :

SELECT  ID  NAME   FROM  EMPLOYEE  -
         WHERE   NAME  LIKE 'smith$'

Match Anything Character ?

The character ? matches any single character. For example,

... WHERE NAME LIKE 'A?e'

finds names containing strings such as:

Aae  Abe  Ace   .......   Axe  Aye  Aze

and also:

A+e  A-e  A*e  A/e  A,e  A.e  A(e  A)e  A'e  A"e

The match anything character can appear more than once in a pattern. The next example, selects all records in which the customer identifier begins with the letters AC followed by any three characters followed by a 9. Notice the use of two special characters, the % and the ?.

SELECT  CUSTID  CUSTNAME ADDRESS PHONE  -
         FROM  CUSTFILE  -
         WHERE   CUSTID  LIKE  '%AC???9'

Classes of Characters [...]

Search for a class or set of characters by enclosing them in square brackets. Some examples of character classes are:

[12]

match all instances of "1" or "2" or both [123]

match all possible combinations of "1", "2", and "3" [a-z]

match lowercase letters [A-Z]

match uppercase letters [0-9]

match decimal digits [J-Q]

match uppercase letters "J" through "Q" [A-Za-z]

match uppercase and lowercase letters For example, to locate information on 1984 accounts. The account identifiers for 1984 begin with an uppercase letter followed by the string "1984". The Where clause might be:

... WHERE  ACCTID  LIKE  '%[A-Z]1984'

Negated Character Class [!...]

To match all lines except those containing the members of a character class, place the negation character ! at the beginning of the class inside the square brackets. For examples:

[!12]

match all characters except "1" and "2" [!a-z]

match all characters except lower case letters [!A-Z]

match all characters except upper case letters [!0-9]

match all characters except decimal digits [!J-Q]

match all characters except upper case letters "J" through "Q" [!A-Za-z]

match all characters except upper and lower case letters For example, to search and delete all rows in which the value of DEPTNUM is not composed entirely of digits.

DELETE  FROM  TCOMPANY.TAB1  -
         WHERE   DEPTNUM  LIKE '[!0-9]'

Closure Character (Zero or More Occurrences) *

To search for strings or patterns of characters that occur an indefinite number of times (known as a closure) specify the closure character "*" after the required pattern.

Some examples of closure patterns are:

a*

zero or more occurrences of lowercase a [A-Z]*

zero or more uppercase letters [Q3x]*

zero or more occurrences of "Q" or "3" or "x" [a-zA-Z]*

For example, to search for all text that appears inside a pair of parentheses :

... WHERE STRING LIKE '(?*)'

The pattern requests all lines that contain "(" followed by zero or more occurrences of any character followed by ")".

Similarly, to search for all Illinois accounts. These are identified in the ACCTID when characters 3 and 4 are "IL" and the last two (verification) digits are "13". The ACCTID may be 10 to 17 characters long and therefore the last two digits may appear in positions 9-10, 10-11, ..., 16-17. The closure character takes care of this problem.

SELECT  *  FROM  ACCOUNTS  -
         WHERE   ACCTID  LIKE  '%??IL?*13$'

Note the beginning and end of line characters. The % character followed by ??IL makes sure that "IL" appears in position 3 and 4. The $ character preceded by 13 makes sure that "13" appears as the last two digits. The ?* notation means that any number of characters can appear between "IL" and "13".

Closure Character (One or More Occurrences)+

This specifies a search for one or more occurrences of a pattern instead of zero or more occurrences. For example, the following command:

... WHERE STRING LIKE ' [aehrt]+ '

searches for complete words made up of the letters a,e,h,r,t.

The search pattern requests strings containing a blank followed by one or more occurrences of the letters a,e,h,r,t followed by another blank. The lines listed contain words such as "a", "are", "at", "here", "rather", "that", "the", "there", "three", etc.

Escape Character @

The special characters represent instructions sent to the pattern matching routine. Occurrences of these special characters cannot be searched for in the normal way. A search for question marks in a field cannot be specified as:

... WHERE string LIKE '?'

because this command will match every character.

The escape character @ is provided to handle this situation. Precede any special character with @, and the character is treated literally. Thus to search for question marks enter:

.... WHERE string LIKE '@?'

In addition, special characters lose their meaning (i.e. the escape character should not be used) when they appear out of context as follows:

%

when not at the beginning of the pattern $

when not at the end of the pattern *

at the beginning of the pattern +

at the beginning of the pattern !

not at the beginning of a character class -

at the beginning or end of a character class Special characters do not apply in the specification of a character class except for:

!

at the beginning of the character class -

in the middle of the character class @@

anywhere in the character class