18-1.

The CONCAT function or the concatenation operator does the trick. The CONCAT function has two string arguments that it returns concatenated. The concatenation operator is specified as || and takes its arguments from its left and right sides.

You may be interested to know that || is actually a function that is itself declared in the STANDARD package:

DECLARE
  first_three  VARCHAR2(3) := 'ABC';
  second_three VARCHAR2(3) := 'DEF';
  third_three  VARCHAR2(3) := 'GHI';
  whole_thing  VARCHAR2(9);
BEGIN
  /*
    || The CONCAT function requires multiple steps to
    || concatenate multiple strings
  */
  whole_thing := CONCAT(first_three,second_three);
  whole_thing := CONCAT(whole_thing,third_three);
  DBMS_OUTPUT.PUT_LINE(whole_thing);
  /*
    || The concatenation operator concatenates multiple
    || very quick and easy
  */
  whole_thing := first_three || second_three || third_three;
  DBMS_OUTPUT.PUT_LINE(whole_thing);
END;

Note also that both functions perform an on-the-fly conversion of their arguments to VARCHAR2 datatypes:

SQL> EXEC DBMS_OUTPUT.PUT_LINE (CONCAT(1,2))
12
SQL> EXEC DBMS_OUTPUT.PUT_LINE (1 || 2)
12

Remember that if the value to be converted is of the date datatype, National Language Support (NLS) settings will affect the appearance of the resulting string.

18-2.

The two words “in string” will lead you directly to the aptly named INSTR function. This function has two obvious arguments (the string to search and the string to search for). It also has two, not so obvious, but very powerful arguments (where to start and what occurrence to find):

DECLARE
  old_blue_eyes VARCHAR2(14) := 'do be do be do';
  v_temp VARCHAR2(10);
BEGIN
  /*
    || Find the first do three times
  */
  v_temp:= INSTR(old_blue_eyes,'do',1,1);
  v_temp:= INSTR(old_blue_eyes,'do',1);
  v_temp:= INSTR(old_blue_eyes,'do'),
  /*
    || Find the second do by looking for the
    || second occurrence
  */
  v_temp := INSTR(old_blue_eyes,'do',1,2);
  /*
    || Find the second do by looking for the
    || first occurrence after the third character
  */
  v_temp := INSTR(old_blue_eyes,'do',3,1);
  /*
    || Find the very last do (twice)
  */
  v_temp := INSTR(old_blue_eyes,'do',1,3);
  v_temp := INSTR(old_blue_eyes,'do',10,1);
END;

The INSTR function is also quite good about converting its arguments to VARCHAR2:

SQL> EXEC DBMS_OUTPUT.PUT_LINE (INSTR ( 1234567890,4));
4

Be careful with case, though; INSTR is not so forgiving in that regard. This example forever returns a big fat zero:

SQL> EXEC DBMS_OUTPUT.PUT_LINE ( INSTR( 'Looter!','l'));
0

18-3.

The REPLACE function can substitute sections of a string, or the TRANSLATE function can substitute character by character. Both functions have the same three arguments: a string, a string to search for, and a string to replace. They work in different ways, though.

Use REPLACE when you want to substitute every occurrence of one string in another:

SQL> EXEC DBMS_OUTPUT.PUT_LINE
   (REPLACE ('Rain, Rain and More Rain','Rain','Sunshine'));

Sunshine, Sunshine and More Sunshine

Use TRANSLATE to substitute every occurrence of single characters in a string. For example, this code substitutes A for C, and C for A, to return CCCBAAA:

SQL> EXEC DBMS_OUTPUT.PUT_LINE(TRANSLATE('AAABCCC','AC','CA'));
CCCBAAA

18-4.

Since the number of characters in a string is equal to its length, the LENGTH function applies here:

v_string_length := LENGTH(v_the_string);

Don’t forget that a blank space is considered a character:

LENGTH('Hi There') <> LENGTH(' Hi There ')

18-5.

Here’s the output:

SQL> EXEC DBMS_OUTPUT.PUT_LINE(LENGTH(1 * 100));
3

3 is the number of characters in 100 (100 * 1). Note that the LENGTH function is quite gracious about converting numeric values to VARCHAR2.

18-6.

Here’s the code:

UPDATE employee
   SET last_name = UPPER(last_name)
 WHERE last_name != UPPER(last_name);

18-7.

Why, UPPER and LOWER, of course. These functions change the case of the whole string and are useful for making case-neutral comparisons in code, for example:

IF UPPER('Procurement') = 'PROCUREMENT'...

18-8.

Here’s a suggested block:

BEGIN
  /*
    || The handy dandy SUBSTR function does the trick with
    || its last two arguments, start location and length.
    || Here we say start at the fourth character and show
    || it plus the next two.
  */
  DBMS_OUTPUT.PUT_LINE(SUBSTR('BRUSHLOTS',4,3));
END;

18-9.

A string can be padded on the left side or the right side by LPAD and RPAD, respectively. Each of these functions returns the padded string specified by three arguments: the string to pad, the length to pad to, and the characters to pad with (a space by default).

DECLARE
  v_temp VARCHAR2(30);
BEGIN
  v_temp := RPAD('Pad with blanks',30);
  v_temp := RPAD('Pad with stars',30,'*'),
  v_temp := RPAD('Pad with stars and tildes',30,'*~'),
  v_temp := RPAD('Will be truncated at 3',3,'*'),
END;

18-10.

The INITCAP function does the trick here to change the initial letter of every word to capitals:

SQL> EXEC DBMS_OUTPUT.PUT_LINE(INITCAP('this is a headline'));
This Is A Headline

18-11.

The LIKE function in SQL can eliminate the unwanted rows from the table. Then all that is required is to loop through each record in the query:

DECLARE
  CURSOR C1 IS SELECT * FROM EMPLOYEE WHERE UPPER(LAST_NAME) LIKE '%E%';
BEGIN
  FOR C1_REC IN C1 LOOP
    DBMS_OUTPUT.PUT_LINE(C1_REC.LAST_NAME||', '||C1_REC.FIRST_NAME);
  END LOOP;
END;

18-12.

The INSTR function can calculate this number. Recall that one of the parameters INSTR accepts is the occurrence of the string you wish to find. By using this, you need only place a single call to INSTR to satisfy the requirement:

SQL> EXEC DBMS_OUTPUT.PUT_LINE (instr(upper('Steven Feuerstein'),'E', 1, 2));
5

18-13.

Here’s the block:

BEGIN
/*
|| Utilize the little known but very effective ability to start searching
|| from the end of the string by specifying a negative third argument for
|| the INSTR function.
||
|| I must confess that I have written many a function to do just this
|| sort of thing in the past without realizing INSTR could do it on its
|| own (DH)
*/
   DBMS_OUTPUT.PUT_LINE(INSTR(UPPER('Steven Feuerstein'),'E',-1,3));
END;

18-14.

The LTRIM function does this nicely by specifying that all zeros be removed from the left side of the string as follows:

LTRIM(my_formatted_number,'0')

18-15.

Hmm, what function is used to left-pad a string? Why, LPAD of course! And since you know that blanks are considered characters (as per a string’s length), write the following:

/* we also know that blanks are the default padding character */
v_number_string := LPAD(v_number_string,15);

18-16.

This does the trick:

DECLARE
  v_new_name VARCHAR2(50);
BEGIN
  /*
    || Because we are only replacing a single character the
    || REPLACE or TRANSLATE function will suffice
  */
  v_new_name := REPLACE('Steven Feuerstein','e','z'),
  v_new_name := TRANSLATE('Steven Feuerstein','e','z'),
END;

18-17.

Here you go:

DECLARE
  v_new_name VARCHAR2(50);
BEGIN
  /*
    || The replace function can be used to substitute nothing
    || for a string
  */
  v_new_name := REPLACE('Steven Feuerstein','e',''),
END;

18-18.

Try this replacement:

v_new_string :=
   REPLACE('IF TO_DATE (my_date_str, ''MM-DD-YY'') > SYSDATE','YY','RR'),

18-19.

This code performs the replacement:

v_new_string := REPLACE(UPPER('IF TO_DATE (my_date_str,''MM-
DD-YY'') > TO_DATE (your_date_str,''mmddyy'')'),'YY','RR'),

18-20.

The RPAD command shown here accomplishes what you need by padding the string “-” using the option parameter to specify the pad character of “-”:

RPAD ('-', 80, '-')

18-21.

All that’s displayed is “Sound effects” because RTRIM starts at the end of the string and removes all occurrences of any of the characters it is to remove, until it comes to one that is not in the list. Note that the actual length of the output is 14 (it includes a trailing blank).

18-22.

This string is displayed:

ABCDEF

18-23.

Here is a suggested function:

CREATE OR REPLACE FUNCTION ascii_converter ( p_char IN VARCHAR2 )
                  RETURN INT IS
BEGIN
  -- The ASCII function returns the numeric
  -- equivalent of a character
  RETURN(ASCII(p_char));
END;

18-24.

Here you go:

CREATE OR REPLACE FUNCTION sound_the_same (
                                           p_string1 IN VARCHAR2,
                                           p_string2 VARCHAR2 )
                  RETURN BOOLEAN IS

  -- always be pessimistic
  v_ret_val BOOLEAN := FALSE;

BEGIN
  -- Compare the soundex returns for the two strings
  IF SOUNDEX(p_string1) = SOUNDEX(p_string2) THEN
    v_ret_val := TRUE;
  ELSE
    v_ret_val := FALSE;
  END IF;
  RETURN(v_ret_val);
END;

18-25.

Try this one:

CREATE OR REPLACE FUNCTION crlf RETURN VARCHAR2 IS
BEGIN
  -- Simply return the character associated with
  -- sequence 10 for a carriage return/line feed
  RETURN(CHR(10));
END;

CREATE OR REPLACE FUNCTION tab RETURN VARCHAR2 IS
BEGIN
  -- Simply return the character associated with
  -- sequence 10 for a carriage return/line feed
  RETURN(CHR(9));
END;

18-26.

The REPLACE function can make the desired switch in the string:

DECLARE
  the_text VARCHAR2(100);
BEGIN
  -- It is important to first replace all single quotes with two quotes
  -- to include the quote in the string
  the_text :=
     'IF TO_DATE (my_date_str, ''MM-DD-YY'') >' ||
     'TO_DATE (your_date_str, ''mmddyy'')';
  DBMS_OUTPUT.PUT_LINE(REPLACE(UPPER(the_text),'YY', 'RR'));
END;

The TRANSLATE function cannot be used here because it replaces single letters, not patterns. This results in the following string:

IF TO_DATE (MR_DATE_STR, 'MM-DD-RR') > TO_DATE (ROUR_DATE_STR, 'MMDDRR')

Note that occurrences of a single “Y” have been changed to “R,” and not only the occurrences of “YY” as was intended.

18-27.

This is a suggested implementation:

/* Filename on web page: stripper.sf */
CREATE OR REPLACE FUNCTION NUMBER_STRIPPER ( p_string IN VARCHAR2 )
                  RETURN VARCHAR2 IS
  -- This function removes all numeric digits from the string
  -- passed in.
  v_current_element NUMBER;  -- # of elements in string being processed
  v_char_length     NUMBER;  -- length of parameter
  v_ret_val         VARCHAR2(2000);  -- return value
  v_current_char  VARCHAR2(1); -- the character being processed

BEGIN
  -- initialize the length of the parameter and the current
  -- element number
  v_char_length := LENGTH(p_string);
  v_current_element := 1;

  -- for every character in the string...
 LOOP
    -- exit when there are no more elements...
    EXIT WHEN v_current_element > v_char_length;

    -- get the value of the current character
    v_current_char := SUBSTR(p_string,v_current_element,1);

    -- if the character is not numeric then append it to the
    -- return value. I realize this is not the fastest way to
    -- check if a character is numeric but in keeping with the spirit
    -- of the exercises I did it this way.
    IF TRANSLATE(v_current_char,'1234567890','~~~~~~~~~~') != '~' OR
       v_current_char = '~' THEN
      v_ret_val := v_ret_val || v_current_char;
    END IF;

    -- get the next character in the parameter
    v_current_element := v_current_element + 1;

  END LOOP;  -- every character in the string

  RETURN(v_ret_val);

END;

18-28.

This function returns “in” because it counts 10 characters back from the end to the “i” in “in”, and returns it plus the next two characters.

18-29.

This INSTR returns 2. It finds the “a” in Sandy by counting back 15 characters from the end of the string and continues towards the beginning of the string looking for the second occurrence of “a”. It ignores the “a” in February because it is the first one.

18-30.

CONCAT and REPLACE functions are forgiving when passed a NULL value:

SQL> EXEC DBMS_OUTPUT.PUT_LINE('A' || NULL || 'B'),
AB
SQL> EXEC DBMS_OUTPUT.PUT_LINE(REPLACE('ABC','C',NULL));
AB

SUBSTR is somewhat forgiving as well:

SQL> EXEC DBMS_OUTPUT.PUT_LINE(SUBSTR('Big Steve and Super Dave',15,NULL));
Super Dave

INSTR doesn’t care if NULLs are passed either. Note that this line doesn’t display anything:

EXEC DBMS_OUTPUT.PUT_LINE(INSTR('Big Steve and Super Dave',NULL,1,1));

18-31.

Here is a PL/SQL block that does the job:

BEGIN
  -- Perform a substring starting at the location of the
  -- second e plus one and going for 5 characters (the location of
  -- the fourth e minus the location of the second e minus one)
  DBMS_OUTPUT.PUT_LINE(SUBSTR('Steven Feuerstein',
     INSTR('Steven Feuerstein','e',1,2) + 1,
     INSTR('Steven Feuerstein','e',1,4) -
     INSTR('Steven Feuerstein','e',1,2) - 1));
END;

18-32.

The winning formula is:

m - n + 1

18-33.

Here is the function:

CREATE OR REPLACE FUNCTION betwnstr ( p_string IN VARCHAR2,
                                      p_start  IN VARCHAR2,
                                      p_end    IN VARCHAR2 )
                           RETURN VARCHAR2 IS
BEGIN
  RETURN(SUBSTR(p_string,p_start,p_end - p_start + 1));
END;

18-34.

The PL/SQL block follows:

DECLARE
  -- Cursor to find all occurrences of YY
  -- in the current users source library
  CURSOR curs_find_yy IS
  SELECT *
    FROM USER_SOURCE
   WHERE INSTR(text,'YY',1,1) > 0
  ORDER BY NAME, TYPE, LINE;
BEGIN
  -- for every YY...
  FOR v_yy_rec IN curs_find_yy LOOP

    -- Display all info
    DBMS_OUTPUT.PUT_LINE(v_yy_rec.name || ' ' ||
                         v_yy_rec.type || ' ' ||
                         v_yy_rec.line);
    -- use the TAB function created earlier for distinction
    DBMS_OUTPUT.PUT_LINE(TAB || v_yy_rec.text);

  END LOOP;  -- every YY

END;

18-35.

This function does the trick:

CREATE OR REPLACE FUNCTION qd ( p_string IN VARCHAR2 )
                  RETURN VARCHAR2 IS
  -- The world famous quote doubler function known
  -- the world over for; well, doubling quotes

BEGIN
  -- Simply replace all occurrences of a single
  -- quote with a single quote concatenated with
  -- another single quote
  RETURN(REPLACE(p_string,'''','''' || ''''));
END;

18-36.

Remove:

SQL> EXEC DBMS_OUTPUT.PUT_LINE(REPLACE(LTRIM(REPLACE(-
'abcabcccccI LOVE CHILIabc','abc','@'),'@'),'@','abc'));
ccccI LOVE CHILIabc

18-37.

The statements are:

18-38.

For a suggested implementation, see the function in the rplc.sf file on the book’s web page.

18-39.

For a suggested implementation, see the function in the rinstr.sf file on the book’s web page.

18-40.

An easy application of RPAD does the trick:

/* Filename on web page: rulerstr.sf */
CREATE OR REPLACE FUNCTION rulerstr (len IN INTEGER) RETURN VARCHAR2
IS
   digits CHAR(10) := '1234567890';
BEGIN
   RETURN RPAD (digits, len, digits);
END;
/

18-41.

Check the center.sf file on this book’s web page for a suggested function.

18-42.

Check the betwnstr.sql file on the book’s web page for a possible solution.

18-43.

Here’s a suggested function:

/* Filename on web page: betwnstr.sql */
CREATE OR REPLACE FUNCTION betwnstr (
                           str IN VARCHAR2,
                           start_str IN VARCHAR2,
                           end_str IN VARCHAR2 := NULL )
                    RETURN VARCHAR2 IS

  -- This function returns the characters in str that occur between
  -- the end of start_str and the beginning of end_str.
  --
  -- If start_str does not occur in str or it occurs after end_str
  -- then NULL will be returned.

BEGIN
  /*
    || If start_str does occur in str then perform a substring with the
    || following values :
    ||
    ||    Start Point = location of start_str + length of start_str
    ||    Length      = location of end_str - Start Point
    ||
    || Not that if end_str is NULL the natural behaviour
    || of SUBSTR will return all
    || characters after start_str
  */
  IF INSTR(str,start_str,1,1) > 0 THEN
    RETURN(SUBSTR(str,INSTR(str,start_str,1,1) + LENGTH(start_str),
         INSTR(str,end_str,1,1) -
       ( INSTR(str,start_str,1,1) + LENGTH(start_str))));
  ELSE
    RETURN(str);
  END IF;

END;

18-44.

This function trims a pattern:

/* Filename on web page: trim.sf */
CREATE OR REPLACE FUNCTION PATTERN_TRIM ( p_string IN VARCHAR2,
                                          p_trim   IN VARCHAR2 )
                  RETURN VARCHAR2 IS
   /*
     || This function trims all occurrences of p_trim from the
     || start of p_string using recursion.
   */

BEGIN
  /*
    || If the first n characters of p_string are equal to p_trim
    || (where n equals the length of p_trim) then call pattern_trim
    || again with the first n characters removed from p_string otherwise
    || return p_string
  */
  IF SUBSTR(p_string,1,LENGTH(p_trim)) = p_trim THEN
    RETURN(PATTERN_TRIM(SUBSTR(p_string,LENGTH(p_trim) + 1,
                        LENGTH(p_string) - LENGTH(p_trim)),p_trim));
  ELSE
    RETURN(p_string);
  END IF;

END;

18-45.

For a suggested implementation, see the closet_loc.sf file on this book’s web page.

18-46.

The biggest challenge in creating such a function is to come up with the most compact, yet readable (and therefore maintainable) implementation. Here is one possibility:

/* Filename on web page: rulerstr2.sf */
CREATE OR REPLACE FUNCTION rulerstr (
   len IN INTEGER, startat IN INTEGER := 1) RETURN VARCHAR2
IS
   digits     CONSTANT CHAR(10) := '1234567890';
   rev_digits CONSTANT CHAR(10) := '0987654321';

   v_digits CHAR(10) := digits;
   v_start INTEGER := MOD (startat, 10);
BEGIN
   IF startat < 0
   THEN
      /*
      || If negative, then switch to the reverse digits string
      || and change the start to its positive location in the string.
      */
      v_start := 11 + v_start;
      v_digits := rev_digits;

   ELSIF v_start = 0
   THEN
      /* Treat 0 same as 10: start with "0" digit. */
      v_start := 10;
   END IF;

   /* Swap the portions of the digits string to get started right. */
   v_digits := SUBSTR (v_digits, v_start) || SUBSTR (v_digits, 1, v_start-1);

   /* Use RPAD to duplicate the ruler to requested length. */
   RETURN RPAD (v_digits, len, v_digits);
END;
/

18-47.

This function should work:

/* Filename on web page: ascii_converter.pkg */
CREATE OR REPLACE PACKAGE ascii_converter IS
  /*
    || This package converts a string into a PL/SQL
    || table containing the ASCII equivalents of each of
    || its characters.
  */

  -- a global collection for the function return type
  TYPE ascii_table_type IS TABLE OF INT
    INDEX BY BINARY_INTEGER;

  -- converion function
  FUNCTION convert ( p_string VARCHAR2 )
           RETURN ascii_table_type;

END ascii_converter;
/

CREATE OR REPLACE PACKAGE BODY ascii_converter IS

  FUNCTION convert ( p_string IN VARCHAR2 )
           RETURN ascii_table_type IS
    /*
      || This function converts p_string into a PL/SQL table
      || containing the ASCII equivalents of each character
    */
    v_ascii_table ascii_table_type;  -- local collection
    v_this_char    VARCHAR2(1);      -- current character
    v_this_counter INT := 1;         -- current character counter

  BEGIN
    -- for every character in p_string...
    v_this_char := SUBSTR(p_string,v_this_counter,1);
    LOOP

      -- exit when no more characters
      EXIT WHEN v_this_char IS NULL;

      -- put the ASCII value of the current character into the
      -- nexte element of the PL/SQL table
      v_ascii_table(v_this_counter) := ASCII(v_this_char);

      -- increment the counter and try the next character
      v_this_counter := v_this_counter + 1;
      v_this_char := SUBSTR(p_string,v_this_counter,1);

    END LOOP;  -- every character in p_string

  END convert;

END ascii_converter;