Sunday 26 August 2012

Highlight Search Term

APEX can highlight the search string in a report if you enter the substitution string in the column property of your report - Highlight Words. Unfortunatelly it will higlight the first occurence only. If you want to highlight more then only one occurence then you can use this function. The only limitation is the number of characters you can return per column (4000):
   FUNCTION get_string_highlight (p_string IN VARCHAR2, p_highlight IN VARCHAR2)
      RETURN VARCHAR2
   IS
      v_string           VARCHAR2 (4000);
      v_substring        VARCHAR2 (4000);
      v_trailingstring   VARCHAR2 (4000);
      v_newstring        VARCHAR2 (32000);
      v_position         NUMBER           := 0;
      v_length           NUMBER           := LENGTH (p_highlight);
      v_span_start       VARCHAR2 (400)
                               := '<span style="color:red;font-weight:bold">';
      v_span_end         VARCHAR2 (400)   := '</span>';
   BEGIN
      IF p_highlight IS NOT NULL
      THEN
         v_string := p_string;

         FOR i IN 1 .. 20
         LOOP
            v_position := INSTR (UPPER (v_string), UPPER (p_highlight));

            IF v_position > 0
            THEN
               v_substring := SUBSTR (v_string, 1, v_position + v_length);
               v_substring :=
                     SUBSTR (v_string, 1, v_position - 1)
                  || v_span_start
                  || SUBSTR (v_string, v_position, v_length)
                  || v_span_end;
               v_string := SUBSTR (v_string, v_position + v_length);
               v_newstring := v_newstring || v_substring;
            END IF;

            EXIT WHEN v_position = 0;
         END LOOP;

         v_newstring := v_newstring || v_string;
      ELSE
         v_newstring := p_string;
      END IF;

      RETURN v_newstring;
   END get_string_highlight;