Searching the Catalog

You'll now see a way to search the product catalog for products containing a number of words in their name or description. You can do either an all-words search or an any-words search in the catalog.

In an all-words search, you're looking for products containing all the words in the search string. In any-words search, you're looking for products containing any of the words in the search string.

Note

Each database product has special features and options for doing advanced full-text searches, with ranking, word similarities, and other complex options. Full-text searches are usually done using the CONTAINS keyword, but it works only if the table is full-text indexed. Full-text searching and indexing is a database-specific feature that isn't part of SQL-99 and isn't covered in this book.

The Basics

The following query searches for devil mask with an all-words search. This means you're searching for products having both words (devil and mask) in their name.:

SELECT Name, Description
FROM Product
WHERE (Description LIKE '%devil%' OR Name LIKE '%devil%')
  AND (Description LIKE '%mask%' OR Name LIKE '%mask%');

Using the sample data presented earlier in the chapter, this query generates the following:

   Name                             Description

   ------------------------    -------------------------------
   Horned Devil Mask           Full devil mask with horns. The
                                   perfect Halloween disguise!

Note the percent wildcard character, which replaces any string of zero or more characters. The expression Description LIKE '%devil%' returns true for any description that contains the word devil.

You can shorten the previous SQL query a bit by using a neat trick: You can concatenate Description and Name and search for the words in the resulted string. String concatenation is performed differently by databases. Here's how to do it with SQL Server:

SELECT Name, Description
FROM Product
WHERE (Description + Name LIKE '%devil%')
  AND (Description + Name LIKE '%mask%');

With Oracle and DB2, you use || instead of +:

SELECT Name, Description
FROM Product
WHERE (Description || Name LIKE '%devil%')
  AND (Description || Name LIKE '%mask%');

MySQL uses the CONCAT function:

SELECT Name, Description
FROM Product
WHERE (CONCAT(Description, Name) LIKE '%devil%')
  AND (CONCAT(Description, Name) LIKE '%mask%');

There are alternative ways of achieving the same result. For example, with MySQL you can use the INSTR function, which returns the position of the first occurrence of a substring in a string. Here's how to use it:

SELECT Name, Description
FROM Product
WHERE INSTR(CONCAT(Description, Name), 'devil') > 0
  AND INSTR(CONCAT(Description, Name), 'mask')  > 0;

So far we've presented examples with all-words searches. For any-words searches, you just need to use OR instead of AND in the WHERE clause:

SELECT Name, Description
FROM Product
WHERE (Description LIKE '%devil%' OR Name LIKE '%devil%')
   OR (Description LIKE '%mask%' OR Name LIKE '%mask%');

Using this sample data, this returns the following:

   Name                              Description

   ---------------------           -------------------------------------
   Beast Mask                     Red-eyed and open-mouthed scary mask
                                         guaranteed to scare!
   Horned Devil Mask              Full devil mask with horns. The perfect
                                         Halloween disguise!
   Sequinned Devil Horns          Shiny red horns for the little devil
                                         inside you!
   Devil Horn Boppers             These red glitter boppers are
                                         guaranteed to attract attention.
                                         They will soon be under your spell!

The SearchCatalog Stored Procedure

The previous statements work fine, but they need to be dynamically generated by the application and sent to the database as such. If you want to store them as stored procedures, things become a bit more complicated.

First, you need to get the words to search for as stored procedure parameters. You can optionally receive a single string and split it into separate words, but this would typically result in a messy, slow, and database-specific stored procedure.

In the following stored procedures, you receive a parameter named AllWords that specifies whether you do an all-words or an any-words search. You also take five string parameters, having a default value of NULL (so you don't have to specify values for all of them when calling the procedure), which will contain the words for which you're looking. It's simple to add more parameters and allow the stored procedure to handle more input parameters.

Tip

Some people think it's dangerous to pose this kind of limitation to the end user (such as not processing more than a fixed number of words from the search string).Well, this may be true, but this limitation doesn't stop people from using Google (which has a limitation of 10 words).

SQL Server

The following is the SQL Server version of the SearchCatalog stored procedure. The @AllWords parameter is a BIT data type, so it can receive values of 0 or 1:

CREATE PROCEDURE SearchCatalog
(@AllWords bit,
@Word1 varchar(15) = NULL,
@Word2 varchar(15) = NULL,
@Word3 varchar(15) = NULL,
@Word4 varchar(15) = NULL,
@Word5 varchar(15) = NULL)
AS

IF @AllWords = 0
   SELECT Name, Description
   FROM Product
   WHERE (Name + Description LIKE '%'+@Word1+'%')
      OR (Name + Description LIKE '%'+@Word2+'%' AND @Word2 IS NOT NULL)
      OR (Name + Description LIKE '%'+@Word3+'%' AND @Word3 IS NOT NULL)
      OR (Name + Description LIKE '%'+@Word4+'%' AND @Word4 IS NOT NULL)
      OR (Name + Description LIKE '%'+@Word5+'%' AND @Word5 IS NOT NULL)

IF @AllWords = 1
   SELECT Name, Description
   FROM Product
   WHERE (Name + Description LIKE '%'+@Word1+'%')
     AND (Name + Description LIKE '%'+@Word2+'%' OR @Word2 IS NULL)
     AND (Name + Description LIKE '%'+@Word3+'%' OR @Word3 IS NULL)
     AND (Name + Description LIKE '%'+@Word4+'%' OR @Word4 IS NULL)
     AND (Name + Description LIKE '%'+@Word5+'%' OR @Word5 IS NULL);

Although it's quite lengthy, the stored procedure isn't complicated. The important thing to understand is the logic of the SELECT statements. In an anywords search, because the conditions are tied with OR, you need to make sure they return False for the NULL words. Also, note that you require at least one word to be provided:

SELECT Name, Description
FROM Product
WHERE (Name + Description LIKE '%'+@Word1+'%')
   OR (Name + Description LIKE '%'+@Word2+'%' AND @Word2 IS NOT NULL)
   OR (Name + Description LIKE '%'+@Word3+'%' AND @Word3 IS NOT NULL)
   OR (Name + Description LIKE '%'+@Word4+'%' AND @Word4 IS NOT NULL)
   OR (Name + Description LIKE '%'+@Word5+'%' AND @Word5 IS NOT NULL)

In an all-words search, this is exactly the opposite. You do require at least a matching word, and the rest of the conditions need to return true for the NULL words (the words that haven't been specified by the calling program):

SELECT Name, Description
FROM Product
WHERE (Name + Description LIKE '%'+@Word1+'%')
  AND (Name + Description LIKE '%'+@Word2+'%' OR @Word2 IS NULL)
  AND (Name + Description LIKE '%'+@Word3+'%' OR @Word3 IS NULL)
  AND (Name + Description LIKE '%'+@Word4+'%' OR @Word4 IS NULL)
  AND (Name + Description LIKE '%'+@Word5+'%' OR @Word5 IS NULL);

It's interesting to see how you dealt with the all-words search, where all words except the first one are also checked if they're NULL. This is required; otherwise, the logical condition will return false if you have NULL words (which is their default value), and the stored procedure wouldn't return any results.

Now let's search for the words devil and mask using an all-words search:

EXECUTE SearchCatalog 1, 'devil', 'mask';

Using the sample data provided earlier, the results should look like this:

   Name                                   Description

   ---------------------         ----------------------------------------
   Horned Devil Mask             Full devil mask with horns. The perfect
                                           Halloween disguise!

Now let's do an any-words search using the same words:

EXECUTE SearchCatalog 0, 'devil', 'mask';

The results look like this:

   Name                                  Description

   ---------------------          --------------------------------------
   Beast Mask                     Red-eyed and open-mouthed scary mask
                                       guaranteed to scare!
   Horned Devil Mask              Full devil mask with horns. The perfect
                                       Halloween disguise!
   Sequinned Devil Horns          Shiny red horns for the little devil
                                       inside you!
   Devil Horn Boppers             These red glitter boppers are
                                       guaranteed to attract attention. They
                                       will soon be under your spell!

Oracle

As you learned in Chapter 9, "Using Stored Procedures," you'll create a package that'll help you return records from the stored procedure. Create the Types package like this:

CREATE OR REPLACE PACKAGE Types
AS
    TYPE CursorType IS REF CURSOR;
END;
/

Next, create the SearchCatalog stored procedure like this:

CREATE OR REPLACE PROCEDURE SearchCatalog
(retCursor IN OUT Types.CursorType,
 AllWords IN NUMBER := 0,
 Word1 IN VARCHAR := NULL,
 Word2 IN VARCHAR := NULL,
 Word3 IN VARCHAR := NULL,
 Word4 IN VARCHAR := NULL,
 Word5 IN VARCHAR := NULL)
AS
BEGIN
  IF AllWords <> 1 THEN
    OPEN retCursor FOR
    SELECT Name, Description
    FROM Product
    WHERE (Name||Description LIKE '%'||Word1||'%')
       OR (Name||Description LIKE '%'||Word2||'%' AND Word2 IS NOT NULL)
       OR (Name||Description LIKE '%'||Word3||'%' AND Word3 IS NOT NULL)
       OR (Name||Description LIKE '%'||Word4||'%' AND Word4 IS NOT NULL)
       OR (Name||Description LIKE '%'||Word5||'%' AND Word5 IS NOT NULL);
  ELSE
    OPEN retCursor FOR
    SELECT Name, Description
    FROM Product
    WHERE (Name||Description LIKE '%'||Word1||'%')
      AND (Name||Description LIKE '%'||Word2||'%' OR Word2 IS NULL)
      AND (Name||Description LIKE '%'||Word3||'%' OR Word3 IS NULL)
      AND (Name||Description LIKE '%'||Word4||'%' OR Word4 IS NULL)
      AND (Name||Description LIKE '%'||Word5||'%' OR Word5 IS NULL);
  END IF;
END;
/

To execute an all-words search, execute the procedure and list the results using the following commands in SQL*Plus:

VARIABLE C RefCursor
EXEC SearchCatalog (:C, 1, 'Devil', 'Mask')
PRINT C;

To do an any-words search, execute the stored procedure like this:

VARIABLE C RefCursor
EXEC SearchCatalog (:C, 0, 'Devil', 'Mask')
PRINT C;

DB2

The following is the DB2 version of the SearchCatalog stored procedure. The @AllWords parameter in this case is a SMALLINT data type because DB2 doesn't have a BIT type:

CREATE PROCEDURE DB2ADMIN.SearchCatalog
(i_AllWords SMALLINT,
 i_Word1 VARCHAR(15),
 i_Word2 VARCHAR(15),
 i_Word3 VARCHAR(15),
 i_Word4 VARCHAR(15),
 i_Word5 VARCHAR(15))

RESULT SETS 1
P1: BEGIN

DECLARE curs1 CURSOR WITH RETURN FOR
   SELECT Name, Description FROM Product
   WHERE (Name || Description LIKE '%' ||i_Word1|| '%')
      OR (Name || Description LIKE '%' ||i_Word2|| '%'
                                AND i_Word2 IS NOT NULL)
      OR (Name || Description LIKE '%' ||i_Word3|| '%'
                                AND i_Word3 IS NOT NULL)
      OR (Name || Description LIKE '%' ||i_Word4|| '%'
                                AND i_Word4 IS NOT NULL)
      OR (Name || Description LIKE '%' ||i_Word5|| '%'
                                AND i_Word5 IS NOT NULL);

DECLARE curs2 CURSOR WITH RETURN FOR
   SELECT Name, Description FROM Product
   WHERE (Name || Description LIKE '%' ||i_Word1|| '%')
     AND (Name || Description LIKE '%' ||i_Word2|| '%'
                                OR i_Word2 IS NULL)
     AND (Name || Description LIKE '%' ||i_Word3|| '%'
                                OR i_Word3 IS NULL)
     AND (Name || Description LIKE '%' ||i_Word4|| '%'
                                OR i_Word4 IS NULL)
     AND (Name || Description LIKE '%' ||i_Word5|| '%'
                                OR i_Word5 IS NULL);

IF i_AllWords = 0 THEN
   OPEN curs1;
ELSE
   OPEN curs2;
END IF;

END P1

Because you can't explicitly set the search terms to NULL in your stored procedure, you need to enter NULL for any clause that isn't used when the procedure is called. To call this procedure, you can open Command Console and enter the following code:

CALL SearchCatalog(0, 'Devil', 'Mask', NULL, NULL, NULL)