create or replace PACKAGE demo_utils AS
  -- (c) Bond & Pollard Ltd 2022
  -- This software is free to use and modify at your own risk.
  -- Ian Bond, 06/02/2022

  -- Extract nth field from a string where fields are separated by a delimiter. 
  FUNCTION get_field(p_string VARCHAR2, p_position NUMBER, p_delimiter VARCHAR2) RETURN VARCHAR2;
  
  -- Return the most commonly occuring delimiter character in the given string
  FUNCTION get_delimiter(p_string VARCHAR2) RETURN VARCHAR2;
END demo_utils;


create or replace PACKAGE BODY demo_utils AS

  FUNCTION delimiter_position (p_string VARCHAR2, p_start_position NUMBER, p_delim_position NUMBER, p_delimiter VARCHAR2) RETURN NUMBER IS
  /*
   1. Return the position within a string of the Nth delimiter from the start position.
   2. Ignore delimiters between a pair of double quotes.
   3. Fields that are delimited by quotes can contain quotes.
   4. Ignore spaces between fields and delimiters.

   Ian Bond       05/02/2022      Created
  */
    c_quote CONSTANT VARCHAR2(1) := '"';  
    v_start_position NUMBER;
    v_delim_pos NUMBER;
    v_delim_count NUMBER;
    v_current_char VARCHAR2(1);
    v_quotes_open BOOLEAN;
    v_delim_found BOOLEAN;
    v_quote_found BOOLEAN;
    custom_error EXCEPTION;
  BEGIN
    IF LENGTH(p_delimiter) <> 1 THEN
      RAISE custom_error;
    END IF;
    IF LENGTH(p_string) < 1 THEN
      RAISE custom_error;
    END IF;
    v_quotes_open := FALSE;
    v_delim_found := FALSE;
    v_quote_found := FALSE;
    v_delim_count := 0;
    v_delim_pos := 0;
    -- Start searching string from this position
    v_start_position := NVL(p_start_position,1);
    FOR I IN v_start_position..LENGTH(p_string) LOOP
      -- Current char in string
      v_current_char := substr(p_string,I,1);
      -- Flag whether most recent char found that is not a space or quote, is a delimiter
      IF v_current_char = p_delimiter THEN
        v_delim_found := TRUE;
      ELSIF NVL(v_current_char,' ') <> ' ' AND v_current_char <> c_quote THEN
        v_delim_found := FALSE;
      END IF;

      -- Flag whether most recent char found that is not a space or delimiter is a quote
      IF v_current_char = c_quote THEN
        v_quote_found := TRUE;
      ELSIF NVL(v_current_char,' ') <> ' ' AND v_current_char <> p_delimiter THEN
        v_quote_found := FALSE;
      END IF;

      IF v_current_char = c_quote AND (v_delim_found OR I=v_start_position) THEN
        -- Open quotes
        -- Current character is a quote either first in string or previous non-space char was a delimiter
        v_quotes_open := TRUE; 
      ELSIF v_current_char = p_delimiter AND v_quote_found THEN
        -- Close quotes
        -- Current character is a delimiter and previous non-space char was a quote
        v_quotes_open := FALSE;
      END IF;

      IF NOT v_quotes_open THEN
        -- Ignore delimiters inside pairs of open quotes
        IF v_current_char = p_delimiter AND I > v_start_position THEN
          -- Increment count of delimiters found only if character matches delimiter and is not within a pair of quotes
          -- Ignore the first delimiter found if you are starting the search at a delimiter part way along the string
          v_delim_count := v_delim_count +1;
          IF (p_start_position = 1 AND v_delim_count = p_delim_position) OR v_start_position > 1 THEN
            -- Nth delimiter found, mark position and stop searching
            v_delim_pos := I;
            dbms_output.put_line('Delimiter found stop search at :'||to_char(v_delim_pos));
            EXIT;
          END IF;
        END IF;
      END IF;
    END LOOP;
    RETURN v_delim_pos;
  EXCEPTION
    WHEN OTHERS THEN
      RETURN -1;
  END;

  FUNCTION get_field (p_string VARCHAR2, p_position NUMBER, p_delimiter VARCHAR2) RETURN VARCHAR2 IS
  /*
   Return the Nth field within a string, where the fields are separated by a specified delimiter 
   e.g. semicolon, comma or tab character.
   Ignore the delimiters within pairs of double quotes. 
   Strip double quotes from the start and end of the string.

   Example:
   select get_field('field1;"field;;;2";"field"""3";field4',3,';') from dual;

   Result: field3"""3

   Ian Bond       05/02/2022      Created
  */
    c_quote CONSTANT VARCHAR2(1) := '"';
    v_pos1 NUMBER;
    v_pos2 NUMBER;
    v_field VARCHAR2(1000);
  BEGIN
    -- Find the position of the delimiter that marks the start of the field
    IF p_position = 1 THEN
      -- First field starts at position 1 in the string
      v_pos1 := 1;
    ELSE
      -- Nth field starts at the preceding delimiter. Field 3 starts after delimiter 2.
      -- Start searching from the 1st character
      v_pos1 := delimiter_position(p_string, 1,  p_position -1, p_delimiter);
    END IF;

    IF v_pos1 > 0 THEN
      -- First delimiter position found
      -- Find the position of the delimiter that marks the end of the field
      -- Search for the next delimiter from the delimiter at the start of the field
      v_pos2 := delimiter_position(p_string, v_pos1, 1, p_delimiter);
      IF p_position > 1 THEN
        -- For the 2nd field onward the starting position of the field is the next character after the delimiter
        v_pos1 := v_pos1 +1;
      END IF;
      IF v_pos2 < 1 THEN
        -- Last field in the string so no end delimiter found
        v_pos2 := LENGTH(p_string)+1;
      END IF;
      -- Strip the double quotes from the start and end of the field
      v_field := TRIM(c_quote FROM TRIM(substr(p_string, v_pos1, v_pos2 - v_pos1)));
    ELSE
      v_field := 'ERROR: Field not found';
    END IF;
    RETURN v_field;
  END;

  FUNCTION get_delimiter (p_string VARCHAR2) RETURN VARCHAR2 IS
  /*
    Return best match for delimiter in string, as most frequently occuring
    of comma, semicolon or tab characters.
    Tab is the default.
  */
    c_tab CONSTANT VARCHAR2(1) := CHR(9);
    v_count_comma NUMBER :=0;
    v_count_semi NUMBER :=0;
    v_count_tab NUMBER :=0;
    v_delimiter VARCHAR2(1);
    v_current VARCHAR2(1);
  BEGIN
    FOR I IN 1..LENGTH(p_string) LOOP
      v_current := SUBSTR(p_string,I,1);
      IF v_current = ';' OR v_current = ',' OR v_current = c_tab THEN
        CASE v_current
          WHEN ';'   THEN v_count_semi := v_count_semi +1;
          WHEN ','   THEN v_count_comma := v_count_comma +1;
          ELSE v_count_tab := v_count_tab +1;
        END CASE;
      END IF;
    END LOOP;
    IF v_count_semi > v_count_comma AND v_count_semi > v_count_tab THEN
      v_delimiter := ';';
    ELSIF v_count_comma > v_count_semi AND v_count_comma > v_count_tab THEN
      v_delimiter := ',';
    ELSE
      v_delimiter := c_tab;
    END IF;
    RETURN v_delimiter;
  END;
END demo_utils;