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)
      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>';
      IF p_highlight IS NOT NULL
         v_string := p_string;

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

            IF v_position > 0
               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;
         v_newstring := p_string;
      END IF;

      RETURN v_newstring;
   END get_string_highlight;

No comments: