oos_util_string.pkb

create or replace package body oos_util_string
as

  /**
   * Converts parameter to varchar2
   *
   * Notes:
   *  - Code copied from Logger: https://github.com/OraOpenSource/Logger
   *
   * @issue 11
   *
   * @example
   *
   * select oos_util_string.to_char(123)
   * from dual;
   *
   * OOS_UTIL_STRING.TO_CHAR(123)---
   * 123
   *
   * @author Martin D'Souza
   * @created 07-Jun-2014
   * @param p_val Number
   * @return string value for p_val
   */
  function to_char(
    p_val in number)
    return varchar2
    deterministic
  as
  begin
    return sys.standard.to_char(p_val);
  end to_char;

  /**
   * See first `to_char`
   *
   * @example
   * select oos_util_string.to_char(sysdate)
   * from dual;
   *
   * OOS_UTIL_STRING.TO_CHAR(SYSDATE)---
   * 26-APR-2016 13:57:51
   *
   * @param p_val Date
   * @return string value for p_val
   */
  function to_char(
    p_val in date)
    return varchar2
    deterministic
  as
  begin
    return sys.standard.to_char(p_val, oos_util.gc_date_format);
  end to_char;

  /**
   * See first `to_char`
   *
   * @example
   * select oos_util_string.to_char(systimestamp)
   * from dual;
   *
   * OOS_UTIL_STRING.TO_CHAR(SYSTIMESTAMP)---
   * 26-APR-2016 13:58:24:851908000 -06:00
   *
   * @param p_val Timestamp
   * @return string value for p_val
   */
  function to_char(
    p_val in timestamp)
    return varchar2
    deterministic
  as
  begin
    return sys.standard.to_char(p_val, oos_util.gc_timestamp_format);
  end to_char;

  /**
   * See first `to_char`
   *
   * @example
   * TODO
   * @param p_val Timestamp with TZ
   * @return string value for p_val
   */
  function to_char(
    p_val in timestamp with time zone)
    return varchar2
    deterministic
  as
  begin
    return sys.standard.to_char(p_val, oos_util.gc_timestamp_tz_format);
  end to_char;

  /**
   * See first `to_char`
   *
   * @example
   * TODO
   *
   * @param p_val Timestamp with local TZ
   * @return string value for p_val
   */
  function to_char(
    p_val in timestamp with local time zone)
    return varchar2
  as
  begin
    return sys.standard.to_char(p_val, oos_util.gc_timestamp_tz_format);
  end to_char;

  /**
   * See first `to_char`
   *
   * @example
   * begin
   *   dbms_output.put_line(oos_util_string.to_char(true));
   *   dbms_output.put_line(oos_util_string.to_char(false));
   * end;
   * /
   *
   * TRUE
   * FALSE
   *
   * @param p_val Boolean
   * @return string value for p_val
   */
  function to_char(
    p_val in boolean)
    return varchar2
    deterministic
  as
  begin
    return case when p_val then 'TRUE' else 'FALSE' end;
  end to_char;

  /**
   * Truncates a string to ensure that it is not longer than `p_length`
   * If length of `p_str` is greater than `p_length` then an ellipsis (`...`) will be appended to string
   *
   * Supports following modes:
   *  - By length (default): Will perform a hard parse at `p_length`
   *  - By word: Will truncate at logical word break
   *
   *
   * @issue #5
   *
   * @example
   * select
   *   oos_util_string.truncate(
   *     p_str => comments,
   *     p_length => 20,
   *     p_by_word => 'N'
   *   ) by_word_n,
   *   oos_util_string.truncate(
   *     p_str => comments,
   *     p_length => 20,
   *     p_by_word => 'Y'
   *   ) by_word_y
   * from apex_dictionary
   * where 1=1
   *   and rownum <= 5
   * ;
   *
   * BY_WORD_N            BY_WORD_Y
   * -------------------- --------------------
   * List of APEX buil... List of APEX...
   * Identifies the th... Identifies the...
   * Identifies the na... Identifies the...
   * Identifies the th... Identifies the...
   * Identifies a work... Identifies a...
   *
   * @author Martin D'Souza
   * @created 05-Sep-2015
   * @param p_str String to truncate
   * @param p_length Max length of final string
   * @param p_by_word Y/N. If Y then will truncate to last word possible
   * @param p_ellipsis ellipsis "..." default
   * @return Trimmed string
   */
  function truncate(
    p_str in varchar2,
    p_length in pls_integer,
    p_by_word in varchar2 default 'N',
    p_ellipsis in varchar2 default '...')
    return varchar2
  as
    l_stop_position pls_integer;
    l_str varchar2(32767) := trim(p_str);
    l_by_word boolean := false;

    l_max_length pls_integer := p_length - length(p_ellipsis); -- This is the max that the string can be without an ellipsis appended to it.

    $if dbms_db_version.version >= 12 $then
      pragma udf;
    $end
  begin

    -- #122 return null if string is null. Doing first since no need to do extra work if null.
    if l_str is null then
      return null;
    end if;

    -- TODO mdsouza: look at the cost of doing these checks
    oos_util.assert(upper(nvl(p_by_word, 'N')) in ('Y', 'N'), 'Invalid p_by_word. Must be Y/N');
    oos_util.assert(p_length > 0, 'p_length must be a postive number');

    if upper(nvl(p_by_word, 'N')) = 'Y' then
      l_by_word := true;
    end if;

    if length(l_str) <= p_length then
      l_str := l_str;
    elsif length(p_ellipsis) > p_length or l_max_length = 0 then
      -- Can't replace string with ellipsis if it'll return a larger string.
      l_str := substr(l_str, 1, p_length);
    elsif not l_by_word then
      -- Truncate by length
      l_str := trim(substr(l_str, 1, l_max_length)) || p_ellipsis;
    elsif l_by_word then
      -- If string at [max string(length) - ellipsis] and next characters belong to same word
      -- Then need to go back and find last non-word
      if regexp_instr(l_str, '\w{2,}', l_max_length, 1, 0) = l_max_length then
        l_str := substr(
            l_str,
            1,
            -- Find the last non-word and go back one character
            regexp_instr(substr(l_str,1, p_length - length(p_ellipsis)), '\W+\w*$') -1);

        if l_str is null then
          -- This will happen if the length is just slightly greater than the elipsis and first word is long
          l_str := substr(trim(p_str), 1, l_max_length);
        end if;

      else
        -- Find last non-word. Need to reverse the string since Oracle regexp doesn't support lookbehind assertions
        l_str := reverse(substr(l_str,1, l_max_length));
        l_str :=
          -- Unreverse string
          reverse(
            -- Cut the string from the first word char to the end in the reveresed string
            -- Since this is a reversed string, the first word char, is really the last word char
            substr(l_str, regexp_instr(l_str, '\w'))
          );

      end if;

      l_str := l_str || p_ellipsis;

      -- end l_by_word
    end if;

    return l_str;
  end truncate;

  /**
   * Does string replacement similar to C's sprintf
   *
   * Notes:
   *  - Uses the following replacement algorithm (in following order)
   *    - Replaces `%s<n>` with `p_s<n>`
   *    - Occurrences of `%s` (no number) are replaced with `p_s1..p_s10` in order that they appear in text
   *    - `%%` is escaped to `%`
   *
   * @example
   * select oos_util_string.sprintf('hello %s', 'martin') demo
   * from dual;
   *
   * DEMO
   * ------------------------------
   * hello martin
   *
   * select oos_util_string.sprintf('%s2, %s1', 'Firstname', 'Lastname') demo
   * from dual;
   *
   * DEMO
   * ------------------------------
   * Lastname, Firstname
   *
   * @issue #8
   *
   * @author Martin D'Souza
   * @created 15-Jun-2014
   * @param p_str Messsage to format using %s and %d replacement strings
   * @param p_s1..10 Replacement strings
   * @return p_msg with strings replaced
   */
  function sprintf(
    p_str in varchar2,
    p_s1 in varchar2 default null,
    p_s2 in varchar2 default null,
    p_s3 in varchar2 default null,
    p_s4 in varchar2 default null,
    p_s5 in varchar2 default null,
    p_s6 in varchar2 default null,
    p_s7 in varchar2 default null,
    p_s8 in varchar2 default null,
    p_s9 in varchar2 default null,
    p_s10 in varchar2 default null)
    return varchar2
  as
    l_return varchar2(4000);
    c_substring_regexp constant varchar2(10) := '%s';

  begin
    l_return := p_str;

    -- Replace %s<n> with p_s<n>
    -- #23: Need to do in reverse so 10 processes before 1
    for i in reverse 1..10 loop
      l_return := regexp_replace(l_return, c_substring_regexp || i,
        case
          when i = 1 then p_s1
          when i = 2 then p_s2
          when i = 3 then p_s3
          when i = 4 then p_s4
          when i = 5 then p_s5
          when i = 6 then p_s6
          when i = 7 then p_s7
          when i = 8 then p_s8
          when i = 9 then p_s9
          when i = 10 then p_s10
          else null
        end,
        1,0,'c');
    end loop;

    -- Replace any occurences of %s with p_s<n> (in order) and escape %% to %
    l_return := sys.utl_lms.format_message(l_return,p_s1, p_s2, p_s3, p_s4, p_s5, p_s6, p_s7, p_s8, p_s9, p_s10);

    return l_return;

  end sprintf;
  
  
  /**
   * Does string replacement of keys by values
   *
   * @example
   * SELECT oos_util_string.sprintf('Hello {firstName} {lastName}!', 
   *                                 t_tab_key_value(t_rec_key_value('firstName', 'Nuno'),
   *                                                 t_rec_key_value('lastName', 'Alves')
   *                                                 )
   *                               ) demo
   * FROM dual;
   *
   * DEMO
   * ------------------------------
   * Hello Nuno Alves!
   *
   * @issue #134
   *
   * @author Nuno Alves
   * @created 12-Nov-2016
   * @param p_str Messsage to format using %s and %d replacement strings
   * @param p_key_values Nested table of key value pairs to be replaced
   * @param p_left_pattern Left string pattern, '{' by default
   * @param p_right_pattern Right string pattern, '}' by default
   * @return p_msg with strings replaced
   */
  function sprintf(
    p_str in varchar2,
    p_key_values in t_tab_key_value,
    p_left_pattern in varchar2 default '{',
    p_right_pattern in varchar2 default '}')
    return varchar2
  as
    l_return varchar2(4000);
  begin
    l_return := p_str;
    if p_key_values IS NOT NULL AND p_key_values.exists(1)
    then
      for i in 1 .. p_key_values.count
      loop
        l_return := REPLACE(l_return, p_left_pattern || p_key_values(i).key || p_right_pattern, p_key_values(i).value);
      end loop;
    end if;
    
    return l_return;
  end sprintf;
  
  
  /**
   * Does string replacement by string position
   *
   * @example
   * select oos_util_string.sprintf('hello {1} {2}!', t_tab_vc2('Nuno','Alves')) demo
   * from dual;
   *
   * DEMO
   * ------------------------------
   * Hello Nuno Alves!
   *
   * select oos_util_string.sprintf('hello {2} {1}!', t_tab_vc2('Nuno','Alves')) demo
   * from dual;
   *
   * DEMO
   * ------------------------------
   * Hello Alves Nuno!
   * @issue #134
   *
   * @author Nuno Alves
   * @created 12-Nov-2016
   * @param p_str Messsage to format using %s and %d replacement strings
   * @param p_tab_vc2 Nested table of replacement strings
   * @param p_left_pattern Left string pattern, '{' by default
   * @param p_right_pattern Right string pattern, '}' by default
   * @return p_msg with strings replaced
   */
  function sprintf(
    p_str in varchar2,
    p_tab_vc2 in t_tab_vc2,
    p_left_pattern in varchar2 default '{',
    p_right_pattern in varchar2 default '}')
    return varchar2
  as
    l_tab_key_value t_tab_key_value := t_tab_key_value();
    l_return varchar2(4000);
  begin
    l_return := p_str;
    if p_tab_vc2 IS NOT NULL AND p_tab_vc2.exists(1)
    then
      for i in 1 .. p_tab_vc2.count
      loop
        l_tab_key_value.extend(1);
        l_tab_key_value(i) := t_rec_key_value(i, p_tab_vc2(i));
      end loop;
    end if;
    
    l_return := sprintf(l_return, l_tab_key_value, p_left_pattern, p_right_pattern);
    
    return l_return;
  end sprintf;

  /**
   * Converts delimited string to array
   *
   * Notes:
   *  - Similar to `apex_util.string_to_table` but handles clobs
   *
   * @issue #32
   *
   * @example
   * declare
   *   l_str clob := 'abc,def,ghi';
   *   l_arr oos_util_string.tab_vc2_arr;
   * begin
   *   l_arr := oos_util_string.string_to_table(p_string => l_str);
   *
   *   for i in 1..l_arr.count loop
   *     dbms_output.put_line('i: ' || i || ' ' || l_arr(i));
   *   end loop;
   * end;
   * /
   *
   * i: 1 abc
   * i: 2 def
   * i: 3 ghi
   *
   * @author Martin Giffy D'Souza
   * @created 28-Dec-2015
   * @param p_string String containing delimited text
   * @param p_delimiter Delimiter
   * @return Array of string
   */
  function string_to_table(
    p_string in clob,
    p_delimiter in varchar2 default gc_default_delimiter)
    return tab_vc2_arr
  is
    l_last_pos pls_integer;
    l_pos pls_integer;

    l_return tab_vc2_arr;
    l_delimiter_len pls_integer := length(p_delimiter);

  begin

    if p_string is not null then
      l_last_pos := 1 - l_delimiter_len; -- If the delimeter length = 1 (most cases) this should be 0. If not need to move back "n" chars
      l_pos := 0;

      while true loop
        l_pos := l_pos + 1;
        l_pos := dbms_lob.instr(p_string, p_delimiter, l_pos, 1);

        if l_pos = 0 then
          l_return(l_return.count + 1) := substr(p_string, l_last_pos + l_delimiter_len); -- Get everything to the end.
          exit;
        else
          l_return(l_return.count + 1) := dbms_lob.substr(p_string, l_pos - (l_last_pos+l_delimiter_len), l_last_pos + l_delimiter_len);
        end if; -- l_pos = 0

        l_last_pos := l_pos;
      end loop;
    end if; -- p_string is not null

    return l_return;
  end string_to_table;

  /**
   * See `string_to_table (p_string clob)` for notes
   *
   * @issue  #32
   *
   * @example
   * -- See previous example
   *
   * @author Martin Giffy D'Souza
   * @created 28-Dec-2015
   * @param p_string String containing delimited text
   * @param p_delimiter Delimiter
   * @return Array of string
   */
  function string_to_table(
    p_string in varchar2,
    p_delimiter in varchar2 default gc_default_delimiter)
    return tab_vc2_arr
  is
    l_clob clob;
    l_return tab_vc2_arr;
  begin
    l_clob := p_string;
    return string_to_table(p_string => l_clob, p_delimiter => p_delimiter);
  end string_to_table;


  /**
   * Converts delimited string to queriable table
   *
   * Notes:
   *  - Text between delimiters must be `<= 4000` characters
   *
   * @example
   *  select rownum, column_value
   *  from table(oos_util_string.listunagg('abc,def'));
   *
   *      ROWNUM COLUMN_VAL
   * ---------- ----------
   *          1 abc
   *          2 def
   *
   * @issue #4
   *
   * @author Martin Giffy D'Souza
   * @created 28-Dec-2015
   * @param p_string String containing delimited text
   * @param p_delimiter Delimiter
   * @return pipelined table
   */
  function listunagg(
    p_string in varchar2,
    p_delimiter in varchar2 default gc_default_delimiter)
    return tab_vc2 pipelined
  is
    l_arr oos_util_string.tab_vc2_arr;
  begin
    l_arr := string_to_table(p_string => p_string, p_delimiter => p_delimiter);

    for i in 1 .. l_arr.count loop
      pipe row (l_arr(i));
    end loop;
  end listunagg;


  /**
   * Converts delimited string to queriable table
   *
   * @issue #4
   *
   * @example
   * See previous example
   *
   * @author Martin Giffy D'Souza
   * @created 28-Dec-2015
   * @param p_string String (clob) containing delimited text
   * @param p_delimiter Delimiter
   * @return pipelined table
   */
  function listunagg(
    p_string in clob,
    p_delimiter in varchar2 default gc_default_delimiter)
    return tab_vc2 pipelined
  is
    l_arr tab_vc2_arr;
  begin
    l_arr := string_to_table(p_string => p_string, p_delimiter => p_delimiter);

    for i in 1 .. l_arr.count loop
      pipe row (l_arr(i));
    end loop;
  end listunagg;


  /**
   * Returns the input string in its reverse order
   *
   * @issue #55
   *
   * @example
   * begin
   *   dbms_output.put_line(oos_util_string.reverse('OraOpenSource'));
   * end;
   * /
   *
   * ecruoSnepOarO
   *
   * @author Tim Nanos
   * @created 31-Mar-2016
   * @param p_string String
   * @return String
   */
  function reverse(
    p_string in varchar2)
    return varchar2
  is
    l_string varchar2(32767);
  begin
    if p_string is not null then
      for i in 1..length(p_string) loop
        l_string := substr(p_string, i, 1) || l_string;
      end loop;
    end if;

    return l_string;
  end reverse;

  /**
   * Returns the input number with the ordinal attached, in english.
   * e.g. 1st, 2nd, 3rd, 4th, etc
   *
   * Notes:
   * - Logic taken from: http://stackoverflow.com/a/13627586/3476713
   *
   * @issue #53
   *
   * @example
   * begin
   *   for i in 1..10
   *   loop
   *     dbms_output.put_line(oos_util_string.ordinal(i));
   *   end loop;
   * end;
   * /
   *
   * @author Trent Schafer
   * @created 1-Aug-2016
   * @param p_num Number
   * @return String
   */
  function ordinal(
    p_num in number)
    return varchar2
  is
    l_mod10 number;
    l_mod100 number;

    l_ordinal varchar2(2);
  begin
    l_mod10 := mod(p_num, 10);
    l_mod100 := mod(p_num, 100);

    if l_mod10 = 1 and l_mod100 != 11
    then
      l_ordinal := 'st';
    elsif l_mod10 = 2 and l_mod100 != 12
    then
      l_ordinal := 'nd';
    elsif l_mod10 = 3 and l_mod100 != 13
    then
      l_ordinal := 'rd';
    else
      l_ordinal := 'th';
    end if;

    return p_num || l_ordinal;
  end ordinal;

end oos_util_string;
/