Skip to main content

Searching for multiple words

I

Imagine a table with products. For simplicity lets use just product name and description.

CREATE TABLE products (
    product_id          NUMBER(10),
    product_name        VARCHAR2(256),
    product_desc        VARCHAR2(4000),
    --
    CONSTRAINT pk_products
        PRIMARY KEY (product_id)
);
--
DELETE FROM products;
INSERT INTO products
SELECT 1, 'First product name, one',    'Some description'      FROM DUAL UNION ALL
SELECT 2, 'Second product name, two',   'Another description'   FROM DUAL UNION ALL
SELECT 3, 'Third product, three',       'Other description'     FROM DUAL;
--
COMMIT;


How would you search for a product with specific words in name and description?

If you want to search for products containing 'First' OR 'one' words in name, then it is quite easy (case search and sanitization of inputs not included).

WITH input AS (
    SELECT /*+ MATERIALIZE */
        'First:one'     AS in_name,
        ''              AS in_desc
    FROM DUAL
)
SELECT p.*
FROM products p
CROSS JOIN input
WHERE REGEXP_LIKE(p.product_name, '(' || REPLACE(input.in_name, ':', '|') || ')');


But what if you want to search for products with 'First' AND 'one' words? And what if you have multiple words? And if you don't know how many? And then if you want to combine multiple columns?

Here is my solution utilizing PL/SQL collections, APEX collections, APEX_STRING_UTIL API and possibly materialized view.

Package specification declaring some data types and APEX collection name for the results:

CREATE OR REPLACE PACKAGE product_app AS

    v_search_results        CONSTANT VARCHAR2(30) := 'SEARCH_RESULTS';  -- collection name

    TYPE product_rec IS RECORD (
        product_id          products.product_id%TYPE,
        product_name        products.product_name%TYPE,
        product_desc        products.product_desc%TYPE
    );
    --
    TYPE product_rows       IS TABLE OF product_rec;
    --
    TYPE product_found      IS TABLE OF products.product_id%TYPE;



    PROCEDURE search_products (
        in_name             products.product_name%TYPE := NULL,
        in_desc             products.product_desc%TYPE := NULL
    );



    FUNCTION get_coll_search_results
    RETURN VARCHAR2
    RESULT_CACHE;

END;
/


And the body handling searches for product names and descriptions.

CREATE OR REPLACE PACKAGE BODY product_app AS

    PROCEDURE search_products (
        in_name                 products.product_name%TYPE := NULL,
        in_desc                 products.product_desc%TYPE := NULL
    )
    AS
        v_products              product_rows;
        v_search_words          APEX_T_VARCHAR2;
        v_found_words           PLS_INTEGER;
        v_found_rows_name       product_found   := product_found();
        v_found_rows_desc       product_found   := product_found();

        -- sanitize inputs
        v_search_name           CONSTANT products.product_name%TYPE := APEX_STRING_UTIL.GET_SLUG(in_name);
        v_search_desc           CONSTANT products.product_desc%TYPE := APEX_STRING_UTIL.GET_SLUG(in_desc);

        -- calculate number of words
        v_target_name           CONSTANT PLS_INTEGER := REGEXP_COUNT(v_search_name, '-') + 1;
        v_target_desc           CONSTANT PLS_INTEGER := REGEXP_COUNT(v_search_desc, '-') + 1;
    BEGIN
        -- cleanup source data before searching
        -- i would suggest to use materialized view or virtual/hidden columns
        SELECT
            p.product_id,
            APEX_STRING_UTIL.GET_SLUG(p.product_name),
            APEX_STRING_UTIL.GET_SLUG(p.product_desc)
        BULK COLLECT INTO v_products
        FROM products p;

        -- search for product name
        IF v_target_name > 0 THEN
            v_search_words := APEX_STRING.SPLIT(v_search_name, '-');
            --
            FOR p IN 1 .. v_products.COUNT LOOP
                v_found_words := 0;
                --
                FOR i IN 1 .. v_search_words.COUNT LOOP
                    -- for whole words you could use APEX_STRING_UTIL.PHRASE_EXISTS
                    IF INSTR(v_products(p).product_name, v_search_words(i)) > 0 THEN
                        v_found_words := v_found_words + 1;
                    END IF;
                END LOOP;
    
                -- if all words found
                IF v_found_words = v_search_words.COUNT THEN
                    v_found_rows_name.EXTEND(1);
                    v_found_rows_name(v_found_rows_name.LAST) := v_products(p).product_id;
                END IF;
            END LOOP;
        END IF;

        -- search for product desc
        IF v_target_desc > 0 THEN
            v_search_words := APEX_STRING.SPLIT(v_search_desc, '-');
            --
            FOR p IN 1 .. v_products.COUNT LOOP
                v_found_words := 0;
                --
                FOR i IN 1 .. v_search_words.COUNT LOOP
                    IF INSTR(v_products(p).product_desc, v_search_words(i)) > 0 THEN
                        v_found_words := v_found_words + 1;
                    END IF;
                END LOOP;
    
                -- if all words found
                IF v_found_words = v_search_words.COUNT THEN
                    v_found_rows_desc.EXTEND(1);
                    v_found_rows_desc(v_found_rows_desc.LAST) := v_products(p).product_id;
                END IF;
            END LOOP;
        END IF;

        -- store results in collection if it match all searching conditions
        APEX_COLLECTION.CREATE_OR_TRUNCATE_COLLECTION(v_search_results);
        --
        FOR p IN 1 .. v_products.COUNT LOOP
            CONTINUE WHEN (NOT v_products(p).product_id MEMBER OF v_found_rows_name AND v_target_name IS NOT NULL);
            CONTINUE WHEN (NOT v_products(p).product_id MEMBER OF v_found_rows_desc AND v_target_desc IS NOT NULL);
            --
            APEX_COLLECTION.ADD_MEMBER(
                p_collection_name   => v_search_results,
                p_n001              => v_products(p).product_id
            );
        END LOOP;
    END;



    FUNCTION get_coll_search_results
    RETURN VARCHAR2
    RESULT_CACHE
    AS
        PRAGMA UDF;
    BEGIN
        RETURN v_search_results;
    END;

END;
/


With the package in place you can search the products table:
(if you want to run this outside of APEX, you would need the APEX session)

BEGIN
    product_app.search_products (
        in_name     => 'FIRST ONE',
        in_desc     => ''
    );
END;
/


How to see the results?

SELECT p.*
FROM products p
JOIN apex_collections s
    ON s.collection_name    = product_app.get_coll_search_results()
    AND s.n001              = p.product_id;


Comments