\SPEC_ _STR.pdl\\


Package STR


STR Package 12/21/2004 JG
Package contains different procedures and functions to deal with strings
AUDIT TRAIL
VERSION 1.0 3/8/2005
Package created to help processing strings.


Type Summary
 RECORDrec_str(string_value VARCHAR2(4000))
           TYPE is a record of string for string procesing
 TABLE OF VARCHAR2(4000) INDEX BY nulltab_split()
           Type is a collection/array of values returned from the split_array function
 TABLE OF row_str INDEX BY nulltab_str()
           TYPE is a table of rec_str

Method Summary
 BOOLEANis_date(in_value VARCHAR2)
           Function will return a boolean TRUE if a string passed to the function is an exceptable date.
 VARCHAR2is_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.
 VARCHAR2is_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.
 BOOLEANis_lower_case(in_string VARCHAR2)
           Function returns a TRUE if all characters in string are Lower Case
 BOOLEANis_mixed_case(in_string VARCHAR2)
           Function will return a BOOLEAN TRUE if string passed to it has mixed case.
 BOOLEANis_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;
 
 VARCHAR2is_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'.
 BOOLEANis_ordinal(in_value VARCHAR2)
           Function will return a boolean TRUE if the string passed is an ordinal (1st, 2cd, 3rd etc.).
 VARCHAR2is_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.
 BOOLEANis_roman_numeral(in_value VARCHAR2)
           Function returns a boolean TRUE if the string passes is a roman numeral.
 VARCHAR2is_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.
 BOOLEANis_upper_case(in_string VARCHAR2)
           Function returns a TRUE if all characters in string are upper Case
 VARCHAR2remove_dbl_space(in_value VARCHAR2, in_char VARCHAR2)
           Function will remove double space from a string and replace them with a single space.
 VARCHAR2split(in_del_field VARCHAR2, in_pos NUMBER, in_del VARCHAR2)
           Function will split a string.
 tab_splitsplit_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.
 tab_strsplit_pipe(in_del_field VARCHAR2, in_del VARCHAR2)
           Pipelined Function returns a table of one column with the deleminted string passed to it.
 VARCHAR2title(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.
 VARCHAR2toggle(in_value VARCHAR2)
           Function will Toggle the case of string, so what was upper is no lower and vice versa.

Type Detail

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

Method Detail

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.