Search Condition Using LIKE

In most SQL applications, a column value that contains character values can be matched to a pattern of characters for the purpose of retrieving one or more rows from a table. This is often referred to as pattern matching. Pattern matching is useful when a user cannot be specific about the data to be retrieved. For instance:

• You're not sure if someone's last name is Robinson, Robertson, or Robbins. You search using the pattern "Rob".

• You want a list of all employees who live on Newman Avenue, Road or Street. You search using the pattern "Newman".

• You want a list of all employees whose name ends in "man", such as Waterman, Spellman, or Herman. You search using the pattern "man".

The LIKE operator is used in the WHERE clause to enable you to retrieve records that have a partial match with a column value. The LIKE operator has the following format:

WHERE  column-name  LIKE  'pattern'

In Texis the capabilities of the LIKE clause have been exponentially increased through implementation of all features of the Metamorph search engine. Rather than the limited single item string search allowed in traditional SQL applications, Texis allows any valid Metamorph query to be substituted for the 'pattern' following LIKE.

Therefore, in addition to traditional string searches, text fields can be searched with all of Metamorph's pattern matchers to find concepts, phrases, variable expressions, approximations, and numeric quantities expressed as text. These queries can contain multiple search items combining calls to different Metamorph pattern matchers. Intersections of such items can be located in proximity to one another within defined text units such as sentences, paragraphs, or the whole record.

It is this integration of Metamorph through the LIKE clause which brings together intelligent full text searching with relational database technology. For instance, within the confines of the Texis relational database, you can also issue queries to find the following:

• All Research and Development reports covering conceptually similar research done on a field of interest. For example, a request for all research done concerning "red lenses" could discover a report about "rose colored glasses".

• All strategic information reports concerning marketing campaigns over a certain dollar amount. For example, such a request for marketing information about wheels could reveal a "sales" campaign where "twenty-five thousand dollars" was allocated to promote "tires".

• An employee whose name sounds like Shuler who helps fix computer problems. For example, a query for approximately Shuler and computers could find Elaine "Schuller" who works in "data processing". And since you are querying a relational database, you could also pull up her phone extension and call for help.

Full use of the Metamorph query language is discussed in depth in Chapter here. In this section we will concentrate on simple examples to illustrate how the LIKE clause can be used to further qualify WHERE.