Package STR

 BEGIN
   If str.is_number('3') Then
     DBMS_OUTPUT.PUT_LINE('True');
   Else
     DBMS_OUTPUT.PUT_LINE('False');
   End If;
 END;
 

rec_str

  public RECORD rec_str(string_value VARCHAR2(4000))

TYPE is a record of string for string procesing

tab_str

  public TABLE OF row_str INDEX BY null tab_str()

TYPE is a table of rec_str

tab_split

  public TABLE OF VARCHAR2(4000) INDEX BY null tab_split()

Type is a collection/array of values returned from the split_array function

split

  public VARCHAR2 split(in_del_field VARCHAR2, 
                        in_pos NUMBER, 
                        in_del VARCHAR2)

Function will split a string. The maximum size returnable is 4000 bytes and up to 15 fields.
For Exammple:

 SQL> select str.split('a,b,c,d',3,',') from dual;
 STR.SPLIT('A,B,C,D',3,',')
 --------------------------------------------------------------------------------
 c

select str.split('a,b,c,d',3,',') from dual;

Parameters:

in_del_field - string to be split

in_del - delimter to split by

Returns:

Returns a single value from a string, up to 4K long

split_array

  public tab_split split_array(in_del_field VARCHAR2, 
                               in_del VARCHAR2)

Funciton takes a string passed to it and returns it as a collection of the type tab_split. For Example

 DECLARE
   t_split str.tab_split := str.split_array('a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z',',');
   i       INTEGER;
 BEGIN
   FOR i IN t_split.FIRST .. t_split.LAST LOOP
     DBMS_OUTPUT.PUT_LINE(t_split(i));
   END LOOP;
 END;
 /
 

Parameters:

in_del_field - string to be split

in_del - delimter to split by

Returns:

Returns an Array of type str.tab_split with a row for each delimited value in in_del_field.

split_pipe

  public tab_str split_pipe(in_del_field VARCHAR2, 
                            in_del VARCHAR2)

Pipelined Function returns a table of one column with the deleminted string passed to it. For example, you could do the following to return a table of the alphabet...

 SELECT *
 FROM TABLE(str.split_pipe('a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z',','))
 

Parameters:

in_del_field - string to be split

in_del - delimter to split by

Returns:

Returns a Piplined Table of Table Type tab_str with a row for each delimited value in in_del_field.

is_upper_case

  public BOOLEAN is_upper_case(in_string VARCHAR2)

Function returns a TRUE if all characters in string are upper Case

Parameters:

in_string - String that could be upper case

Returns:

Returns a boolean TRUE if string is a upper case string, else a FALSE is returned

is_lower_case

  public BOOLEAN is_lower_case(in_string VARCHAR2)

Function returns a TRUE if all characters in string are Lower Case

Parameters:

in_string - String that could be Lower case

Returns:

Returns a boolean TRUE if string is a Lower case string, else a FALSE is returned

is_mixed_case

  public BOOLEAN is_mixed_case(in_string VARCHAR2)

Function will return a BOOLEAN TRUE if string passed to it has mixed case.

Parameters:

in_string - String that could contain mixed case

Returns:

Returns a boolean TRUE if string is a mixed string, else a FALSE is returned

is_number

  public BOOLEAN is_number(in_value VARCHAR2)

Function will return a boolean TRUE if a string passed to the function is a numeric value for example

 BEGIN
   If str.is_number('3') Then
     DBMS_OUTPUT.PUT_LINE('True');
   Else
     DBMS_OUTPUT.PUT_LINE('False');
   End If;
 END;
 

Parameters:

in_value - Value that is evaluated to see if it is a number

Returns:

Returns a BOOLEAN True if Chacter value passed is considered a number, else return a BOOLEAN False.

is_number_sql

  public VARCHAR2 is_number_sql(in_value VARCHAR2)

Function will return a VARCHAR2 value of 'TRUE' if a string passed to the function is a numeric value, else it will return a value of 'FALSE'. This function was designed to be used in SQL statements since booleans values are only easliy handeled from PL/SQL. For example..

 SQL> SELECT str.is_number_sql('10') FROM dual;
 str.IS_NUMBER_SQL('10')
 --------------------------------------------------------------------------------
 TRUE
 

Parameters:

in_value - Value that is evaluated to see if it is a number

Returns:

Returns a VARCHAR2 value of 'TRUE' if Chacter value passed is considered a number, else 'FALSE' is returned.

is_ordinal

  public BOOLEAN is_ordinal(in_value VARCHAR2)

Function will return a boolean TRUE if the string passed is an ordinal (1st, 2cd, 3rd etc.).

Parameters:

in_value - String to be evaluated

Returns:

Returns a boolean TRUE if string passed is an ordinal, else it returns a FALSE

is_ordinal_sql

  public VARCHAR2 is_ordinal_sql(in_value VARCHAR2)

Function returns like is_ordinal but instead returns a VARCHAR2 value of 'TRUE' if the string passed is an ordinal or returns a value of 'FALSE' if it is not.

Parameters:

in_value - Value that is evaluated to see if it is an ordinal

Returns:

Returns a VARCHAR2 value of 'TRUE' if Chacter value passed is considered an ordianal, else 'FALSE' is returned.

is_roman_numeral

  public BOOLEAN is_roman_numeral(in_value VARCHAR2)

Function returns a boolean TRUE if the string passes is a roman numeral.

 For Example...
 
 BEGIN
   IF str.is_roman_numeral('XXsII') THEN
     Dbms_Output.Put_Line('true');
   ELSE
     Dbms_Output.Put_Line('false');
   END IF;
 END;
 

Parameters:

in_value - String to be evaluated

Returns:

Returns a boolean TRUE if string passed is a roman numeral, else it returns a FALSE

is_roman_numeral_sql

  public VARCHAR2 is_roman_numeral_sql(in_value VARCHAR2)

Function returns like is_roman_numeral but instead returns a VARCHAR2 value of 'TRUE' if the string passed is a roman numeral or returns a value of 'FALSE' if it is not. For Example...

 SQL> SELECT str.is_roman_numeral_sql('XXII') FROM DUAL;
 STR.IS_ROMAN_NUMERAL_SQL('XXII')
 --------------------------------------------------------------------------------
 TRUE
 SQL>
 

Parameters:

in_value - Value that is evaluated to see if it is an ordinal

Returns:

Returns a VARCHAR2 value of 'TRUE' if Chacter value passed is considered an ordianal, else 'FALSE' is returned.

is_date

  public BOOLEAN is_date(in_value VARCHAR2)

Function will return a boolean TRUE if a string passed to the function is an exceptable date. Here is an example...

 BEGIN
   If str.is_date('5-dec-2004') Then
     DBMS_OUTPUT.PUT_LINE('True');
   Else
     DBMS_OUTPUT.PUT_LINE('False');
   End If;
 END;
 

Parameters:

in_value - Value that is evaluated to if it is a date

Returns:

Returns a BOOLEAN True if Chacter value passed is considered a date, else return a BOOLEAN False.

is_date_sql

  public VARCHAR2 is_date_sql(in_value VARCHAR2)

Function evaluates a date in the same way as is_date but returns a VARCHAR2 value of 'TRUE' if the string is a date or a value of 'FALSE' if it is not. For Example....

 SQL> select str.is_date_sql('10-dec-2004') from dual;
 STR.IS_DATE_SQL('10-DEC-2004')
 --------------------------------------------------------------------------------
 TRUE
 

Parameters:

in_value - Value that is evaluated to if it is a date

Returns:

Returns a VARCHAR2 value of 'TRUE' if Chacter value passed is considered a date, else 'FALSE' is returned.

is_date_format

  public VARCHAR2 is_date_format()

Function will return the last date format used to evaluate a date with the is_date function and the is_date_sql function. For example...

 SQL> select str.is_date_sql('10-dec-2004') from dual;
 STR.IS_DATE_SQL('10-DEC-2004')
 --------------------------------------------------------------------------------
 TRUE
 SQL> select str.is_date_format from dual;
 IS_DATE_FORMAT
 --------------------------------------------------------------------------------
 DDMMYYYY
 SQL>
 

Returns:

Returns the date format that evalueted the IS_DATE call.

title

  public VARCHAR2 title(in_value VARCHAR2)

Function works similiar to UPPER and LOWER builtins except that it upper cases Only the first letter of each word and lower cases the rest of the word. Similiar to what MS Word would do if you changed the case of a sentance to Title. For Example...

 SQL> SELECT str.title('joe garrepy') FROM dual;
 str.TITLE('JOEGARREPY')
 --------------------------------------------------------------------------------
 Joe Garrepy
 SQL>
 

Parameters:

in_value - in_value is the string to be formated

Returns:

Returns a VARCHAR2 value with all character string delimeted by spaces displaying an upper case character as the first character of each filed and the rest of the characters in each field displaying lower case.

toggle

  public VARCHAR2 toggle(in_value VARCHAR2)

Function will Toggle the case of string, so what was upper is no lower and vice versa. For Example...

 SQL> SELECT str.toggle('Toggle Case') FROM dual;
 str.TOGGLE('TOGGLECASE')
 --------------------------------------------------------------------------------
 tOGGLE cASE
 SQL>
 

Parameters:

in_value - in_value is the string to be formated

Returns:

Returns the passed string with the Case reversed.

remove_dbl_space

  public VARCHAR2 remove_dbl_space(in_value VARCHAR2, 
                                   in_char VARCHAR2)

Function will remove double space from a string and replace them with a single space. The default remove is a space, but by changing the in_char parameter this will also remove other types of double characters For Example...

 SQL>  select str.remove_dbl_space('Test  of  double  space  .') from dual;
 str.REMOVE_DBL_SPACE('TESTOFDOUBLESPACE.')
 --------------------------------------------------------------------------------
 Test of double space .
 SQL>
 

Parameters:

in_value - Value that will have all double space removed from it.

in_char - Character that you want to replace double occurances out

Returns:

Returns in_value with sigle spaces replacing all double spaces.