Like Predicate (SQL)

Introduction

The SQL Like Predicate tests whether one value matches a pattern.

Syntax

like-predicate ::=
  test-expression like-predicate-part2

like-predicate-part2 ::=
  [ NOT ] LIKE pattern-expression [ ESCAPE escape-character ]

Syntax Details

test-expression is the Value Expression being tested.

NOT is an optional keyword that negates the result

pattern-expression is a Value Expression that specifies the match pattern.

escape-character is the character that is put in front of a wildcard character to indicate the wildcard character should be interpreted as a regular character and not as a wildcard.

The like-predicate-part2 can be used as a test in the when-operand of a Case Function.

Remarks

The predicate X LIKE Y matches the value X with the pattern Y. The pattern Y can contain regular characters or wildcard characters. During pattern matching, regular characters in X must match exactly those in Y. However wildcard characters in Y can match any or multiple characters in X.

Pattern CharacterDescription
%Matches any string of zero or more characters
_ (underscore)Matches any single character

The escape-character is a character that can be placed in front of a wildcard character to interpret the wildcard as a regular character. There is no default for escape-character. If specified, it must be a character string of length 1.

The predicate X NOT LIKE Y is equivalent to NOT( X LIKE Y ). Similarly the predicate X NOT LIKE Y ESCAPE Z is equivalent to NOT( X LIKE Y ESCAPE Z ).

If any of X, Y or Z are NULL, the predicate returns UNKNOWN.

Examples

To find all analog tags ending with 'Pressure':

Select FullName
From TagAnalog
Where "Name" LIKE '%Pressure'

To find all analog tags where the units field ends in % (percent):

Select FullName, Units
From TagAnalog
Where Units LIKE '%\%' ESCAPE '\'

Further Information

Predicates

To learn about other types of predicate.

Fernhill SCADA Structured Query Language (SQL)

To learn more about the SQL features in Fernhill SCADA.

Glossary

For definitions of the terms used in Fernhill SCADA.