Skip to content

Latest commit

 

History

History
610 lines (405 loc) · 9.66 KB

oos_util_string.md

File metadata and controls

610 lines (405 loc) · 9.66 KB

OOS_UTIL_STRING

Constants

Name Code Description
gc_default_delimiter 
gc_default_delimiter constant varchar2(1) := ',';
 Default delimiter for delimited strings
gc_cr 
gc_cr constant varchar2(1) := chr(13);
 Carriage Return
gc_lf 
gc_lf constant varchar2(1) := chr(10);
 Line Feed
gc_crlf 
gc_crlf constant varchar2(2) := gc_cr

TO_CHAR Function

Converts parameter to varchar2

Notes:

Syntax

function to_char(
  p_val in number)
  return varchar2
  deterministic

Parameters

Name Description
p_val Number
return string value for p_val

Example


select oos_util_string.to_char(123)
from dual;

OOS_UTIL_STRING.TO_CHAR(123)---
123

TO_CHAR-1 Function

See first to_char

Syntax

function to_char(
  p_val in date)
  return varchar2
  deterministic

Parameters

Name Description
p_val Date
return string value for p_val

Example

select oos_util_string.to_char(sysdate)
from dual;

OOS_UTIL_STRING.TO_CHAR(SYSDATE)---
26-APR-2016 13:57:51

TO_CHAR-2 Function

See first to_char

Syntax

function to_char(
  p_val in timestamp)
  return varchar2
  deterministic

Parameters

Name Description
p_val Timestamp
return string value for p_val

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

TO_CHAR-3 Function

See first to_char

Syntax

function to_char(
  p_val in timestamp with time zone)
  return varchar2
  deterministic

Parameters

Name Description
p_val Timestamp with TZ
return string value for p_val

Example

TODO

TO_CHAR-4 Function

See first to_char

Syntax

function to_char(
  p_val in timestamp with local time zone)
  return varchar2

Parameters

Name Description
p_val Timestamp with local TZ
return string value for p_val

Example

TODO

TO_CHAR-5 Function

See first to_char

Syntax

function to_char(
  p_val in boolean)
  return varchar2
  deterministic

Parameters

Name Description
p_val Boolean
return string value for p_val

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

TRUNCATE Function

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

Syntax

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

Parameters

Name Description
p_str String to truncate
p_length Max length of final string
p_by_word Y/N. If Y then will truncate to last word possible
p_ellipsis ellipsis "..." default
return Trimmed string

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...

SPRINTF Function

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 %

Syntax

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

Parameters

Name Description
p_str Messsage to format using %s and %d replacement strings
p_s1..10 Replacement strings
return p_msg with strings replaced

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

STRING_TO_TABLE Function

Converts delimited string to array

Notes:

  • Similar to apex_util.string_to_table but handles clobs

Syntax

function string_to_table(
  p_str in clob,
  p_delim in varchar2 default gc_default_delimiter)
  return oos_util.tab_vc2_arr

Parameters

Name Description
p_str String containing delimited text
p_delim Delimiter
return Array of string

Example

declare
  l_str clob := 'abc,def,ghi';
  l_arr oos_util.tab_vc2_arr;
begin
  l_arr := oos_util_string.string_to_table(p_str => 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

STRING_TO_TABLE-1 Function

See string_to_table (p_str clob) for notes

Syntax

function string_to_table(
  p_str in varchar2,
  p_delim in varchar2 default gc_default_delimiter)
  return oos_util.tab_vc2_arr

Parameters

Name Description
p_str String containing delimited text
p_delim Delimiter
return Array of string

Example

-- See previous example

LISTUNAGG Function

Converts delimited string to queriable table

Notes:

  • Text between delimiters must be <= 4000 characters

Syntax

function listunagg(
  p_str in varchar2,
  p_delim in varchar2 default gc_default_delimiter)
  return oos_util.tab_vc2 pipelined

Parameters

Name Description
p_str String containing delimited text
p_delim Delimiter
return pipelined table

Example

 select rownum, column_value
 from table(oos_util_string.listunagg('abc,def'));

     ROWNUM COLUMN_VAL
---------- ----------
         1 abc
         2 def

LISTUNAGG-1 Function

Converts delimited string to queriable table

Syntax

function listunagg(
  p_str in clob,
  p_delim in varchar2 default gc_default_delimiter)
  return oos_util.tab_vc2 pipelined

Parameters

Name Description
p_str String (clob) containing delimited text
p_delim Delimiter
return pipelined table

Example

See previous example

REVERSE Function

Returns the input string in its reverse order

Syntax

function reverse(
  p_str in varchar2)
  return varchar2

Parameters

Name Description
p_str String
return String

Example

begin
  dbms_output.put_line(oos_util_string.reverse('OraOpenSource'));
end;
/

ecruoSnepOarO

ORDINAL Function

Returns the input number with the ordinal attached, in english.
e.g. 1st, 2nd, 3rd, 4th, etc

Notes:

Syntax

function ordinal(
  p_num in number)
  return varchar2

Parameters

Name Description
p_num Number
return String

Example

select oos_util_string.ordinal(level)
from dual
connect by level <= 10;