Programming Oracle Triggers and Stored Procedures (3rd Edition) (Prentice Hall PTR Oracle Series)

2017-07-07 02:10:07

11.13.1 SUBSTR

SUBSTR(str, start_char, [no_of_chars])

`str`	`varchar2`	The input string.
`start_pos`	`integer`	Go to the START_POS character position in the string and count forward, NO_OF_CHARS. If START_POS is negative, start from the tail of the string.
`no_of_chars`	`integer`	Number of characters to select. If not specified, then get the rest of the string.
`retuns`	`varchar2`	Returns the substring or NULL.

The SUBSTR function returns a slice of an input string. The arguments START_POS and NO_OF_CHARS can be literals, variables , or string expressions that evaluate to a number. The following are some SUBSTR examples in a SQL*Plus session.

Given the string, 12345, select the tail of the string starting at character position 2.

SQL> SELECT substr('12345',2) str FROM dual; STR ---- 2345

Given the string 12345678, go to the fourth character from the end ”make that the starting point and select the next two characters.

SQL> SELECT substr('12345678',-4,2) str FROM dual; STR ---- 56

Concatenate the last character and first character, in reverse. The first SUBSTR uses START_POS of negative 1 ”start counting from the right, just one character. This is concatenated with the string's first character.

SQL> SELECT substr('12345',-1)substr('12345',1,1) str FROM dual; STR --- 51

START_POS and NO_OF_CHARS can be an expression. This example uses the INSTR function for the START_POS value. The objective is to select the file name portion from a full pathname. First, use INSTR to return the last position of a forward slash in a string. In this example, the position of the last forward slash is the 10th character position.

SQL> SELECT instr('/aa/bb/cc/dd','/',-1,1) FROM dual; INSTR('/AA/BB/CC/DD','/',-1,1) ------------------------------ 10

To select just the filename from a full pathname, select all remaining characters after the last forward slash ”for the previous select, that would be all characters after the 10th position. This can be generalized to the evaluation of: the INSTR function result plus 1.

Incorporate "INSTR result + 1" into a PL/SQL function. This produces a function that can return the filename portion of a pathname.

FUNCTION filename(v_path IN VARCHAR2) RETURN VARCHAR2 IS BEGIN RETURN substr(v_path, instr(v_path,'/',-1,1)+1); END filename;

Users of this function can extract the filename portion of a string by coding:

my_file_name := filename(v_full_pathname); my_file_name := filename('dir/dir/filename.dbf');

If the value passed, V_FULL_PATHNAME does not contain a forward slash (a caller passed a filename only) and INSTR returns a 0. To this 0 is added a 1. This evaluates to the following:

RETURN substr(v_path, instr(v_path,'/',-1,1)+1); RETURN substr(v_path, 0+1);

The function RETURN value is the original string, which would be the file name.

SUBSTR returns NULL if the START_POS exceeds the length of a string. This can occur if you code a SUBSTR function with a literal START_POS value. The following example uses 10. If the argument passed to this procedure is less than 10 characters, the SUBSTR function result is NULL.

PROCEDURE test (arg IN VARCHAR2) IS declarations BEGIN my_variable := SUBSTR(arg, 10); remaining PL/SQL END

The procedure TEST may not produce the expected results if ARG is less than 10 characters. Avoid situations like this by using NVL and not using literals. You can always check the length of a string with the LENGTH function.

11.13.2 INSTR

INSTR(str1, str2, [start_pos [,occurrence]])

`Str1`	`varchar2`	STR1 is the string being searched.
`Str2`	`varchar2`	We are looking for an occurrence of this string within STR1.
`start_pos`	`integer`	Start looking at this character position.
`occurrance`	`integer`	Look for the first, second, or nth occurrence.
`returns`	`integer`	Returns the character position or 0.

The INSTR function returns a number, which is the character position of the nth occurrence of STR2 within STR1.

Zero is returned if no substring or the requested occurrence of that string cannot be found. To experiment with a comma-delimited string, hard code a string using DUAL. This SQL returns the first occurrence of a comma.

SQL> SELECT instr('aaa,bbb,ccc,ddd',',') FROM dual; INSTR('AAA,BBB,CCC,DDD',',') ---------------------------- 4

The second occurrence of a comma, starting from character position 3, is the 8th character.

SQL> select instr('aaa,bbb,ccc,ddd',',',3,2) from dual; INSTR('AAA,BBB,CCC,DDD',',',3,2) -------------------------------- 8

The third occurrence of a comma, starting from character position 2, is the 12th character.

SQL> select instr('aaa,bbb,ccc,ddd',',',2,3) from dual; INSTR('AAA,BBB,CCC,DDD',',',2,3) -------------------------------- 12

Encapsulate INSTR into a function. The function NEXT_TOKEN is a building block for code that parses delimited strings. This function returns TRUE if the substring is found. An OUT mode parameter, FOUND_AT_POSITION, identifies the location of the substring.

CREATE OR REPLACE FUNCTION next_token (base_string IN VARCHAR2, looking_for IN VARCHAR2, start_looking_at IN INTEGER, found_at_position OUT INTEGER) RETURN BOOLEAN IS result integer; BEGIN found_at_position := INSTR (base_string,looking_for, start_looking_at,1); RETURN (found_at_position <> 0); END next_token;

Each call to NEXT_TOKEN returns the FOUND_AT_POSITION. Hence repeated calls, each passing FOUND_AT_POSITION, will return all occurrences of the token. For example, pass a comma-delimited string and display each FOUND_AT_POSITION. The following is a sequence of calls to NEXT_TOKEN. Each subsequent call begins the search with FOUND_AT_POSITION + 1.

DECLARE str VARCHAR2(100) := 'aaa,bbb,ccc,ddd,eee,fff'; delimeter VARCHAR2(1) := ','; b BOOLEAN; found_at INTEGER; start_point INTEGER := 1; BEGIN b := next_token(str,delimeter,start_point,found_at); dbms_output.put_line(found_at); start_point := found_at + 1; b := next_token(str,delimeter,start_point,found_at); dbms_output.put_line(found_at); start_point := found_at + 1; b := next_token(str,delimeter,start_point,found_at); dbms_output.put_line(found_at); END;

The result of this PL/SQL block is the location of the first three commas.

4 8 12

Rather than code a repeated sequence, use a WHILE LOOP that parses the entire string. The loop continues to cycle provided the return code is TRUE.

DECLARE str VARCHAR2(100) := 'aaa,bbb,ccc,ddd,eee,fff'; delimeter VARCHAR2(1) := ','; b BOOLEAN; found_at INTEGER; start_point INTEGER := 1; BEGIN WHILE (next_token(str,delimeter,start_point,found_at)) LOOP dbms_output.put_line(found_at); start_point := found_at + 1; END LOOP; END;

The WHILE LOOP produces all comma occurrences: 4, 8, 12, 16, and 20. Using this data we can select all fields between the commas. The first field is in character positions 1 “3, the second in positions 5 “7. These are start and stop points of the string. We can use SUBSTR to get these fields, but SUBSTR requires a starting point and a length. We can write an enhancement to SUBSTR. This will be a new function that uses start and stop points. This function is written as SUB_STRING.

CREATE OR REPLACE FUNCTION sub_string (base_string IN VARCHAR2, start_point IN INTEGER, end_point IN INTEGER) RETURN VARCHAR2 IS BEGIN RETURN SUBSTR( base_string, start_point, end_point-start_point+1); END sub_string;

There are currently two string functions: NEXT_TOKEN and SUB_STRING. These functions are likely candidates for a string manipulation package, called STRINGS_PKG. The package specification is shown here.

CREATE OR REPLACE PACKAGE strings_pkg IS FUNCTION next_token (base_string IN VARCHAR2, looking_for IN VARCHAR2, start_looking_at IN INTEGER, found_at_position OUT INTEGER) RETURN BOOLEAN; FUNCTION sub_string (base_string IN VARCHAR2, start_point IN INTEGER, end_point IN INTEGER) RETURN VARCHAR2; END strings_pkg;

There is no need to show the package body. That will consist only of the procedure code for NEXT_TOKEN and SUB_STRING, previously shown. When the final application code is written using the strings package, it has the following form.

DECLARE str VARCHAR2(100) := 'aaa,bbb,ccc,ddd,eee,fff'; delimeter VARCHAR2(1) := ','; b BOOLEAN; found_at INTEGER; start_point INTEGER := 1; BEGIN WHILE (strings_pkg.next_token (str,delimeter,start_point,found_at)) LOOP dbms_output.put_line (strings_pkg.sub_string (str,start_point,found_at-1)); start_point := found_at + 1; END LOOP; dbms_output.put_line(substr(str,start_point)); END;

The output from this application piece now identifies each token in the string.

aaa bbb . . . fff

11.13.3 LPAD, RPAD

LPAD(str1, new_length [,str2]) RPAD(str1, new_length [,str2])

`str1`	`varchar2`	The string to be padded .
`new_length`	`integer`	The new length of the string. Pad the string to make it this long.
`str2`	`integer`	The pad ”append this to STR1. If no STR2 is passed, a BLANK character is used for padding.
`returns`	`varchar2`	The padded string that may be padded with STR2, or actually truncated ”this depends on the length of STR1 and new_length.

The PAD function can be used to pad characters such as a blank or a zero. The pad can be a string ”not just a character. See complementary functions that trim: LTRIM, RTRIM.

The following table contains five SQL statements. The first three statements left-pad a three-character string. The result is a six-character string. The fourth example does no padding because the string is already six characters. The last SQL statement truncates the string ”STR1 exceeds NEW_LENGTH, which is 6, and is truncated.

The SQL statement	Returns
`SQL> SELECT LPAD('abc',6,'0') FROM dual;`	`000abc`
`SQL> SELECT LPAD('abc',6,'01') FROM dual;`	`010abc`
`SQL> SELECT LPAD('abc',6,'01234') FROM dual;`	`012abc`
`SQL> SELECT LPAD('abcdef',6,'01234') FROM dual;`	`abcdef`
`SQL> SELECT LPAD('abcdefg',6,'01234') FROM dual;`	`abcdef`

The LPAD function truncates a string when NEW_LENGTH exceeds the length of the string. A string function can be built with some defensive code that takes this into consideration. The following code replaces NEW_LENGTH with the greater of LENGTH(STR1) and NEW_LENGTH ”this prevents string truncation .

CREATE OR REPLACE FUNCTION left_pad (base_string IN VARCHAR2, new_length IN INTEGER, pad_string IN VARCHAR2) RETURN VARCHAR2 IS BEGIN RETURN LPAD (base_string, GREATEST(LENGTH(base_string), new_length), pad_string); END left_pad;

The LEFT_PAD function will not truncate the string in this call:

dbms_output.put_line(left_pad('abcdefg',6,'01234'));

This DBMS_OUTPUT displays the original seven-character string ”no truncation.

Numeric digits often need formatting. The built-in string functions can be combined to construct functions that provide specific types of padding. The following function uses DECODE, INSTR, and concatenation to return a number as a string, but with two decimal places filled.

CREATE OR REPLACE FUNCTION pad_number(N NUMBER) RETURN VARCHAR2 IS the_pad VARCHAR2(3); the_number VARCHAR2(30) := TO_CHAR(N); BEGIN SELECT DECODE(INSTR(the_number,'.',1), 0, '.00', LENGTH(the_number)-1, '0', LENGTH(the_number)-2, NULL) INTO the_pad FROM dual; RETURN '$'TO_CHAR(N)the_pad; END;

The PAD_NUMBER function returns the string $99.00 when the input is 99 and returns $99.30 when 99.3 is passed.

Built-in functions can be nested. Padding can be accomplished on both sides of a string by nesting LPAD and RPAD. Take a string and left pad 6 characters with a dash, then right pad that result a full 12 characters with an asterisk.

SQL> SELECT RPAD(LPAD('aaa',6,'-'),12,'*') FROM dual; RPAD(LPAD('A ------------ ---aaa******

11.13.4 LTRIM, RTRIM

LTRIM(str1, [,str2]) RTRIM(str1, [,str2])

`str1`	`varchar2`	The string to be trimmed
`str2`	`integer`	This is one or more characters. TRIM looks for repeated occurrences of this string and removes them. The default is blank.
`returns`	`varchar2`	The trimmed string. If the trimming causes all characters to be trimmed, the return is NULL.

The TRIM function removes all repeated occurrences of a string that make up the head or tail of the string. To trim all blanks on either side of a string:

LTRIM(RTRIM(string));

The LTRIM function removes the first six characters:

010101---01---01

Once the LTRIM detects an end to the pattern, at the first dash, no other characters are removed.

The SQL statement	Returns
`SELECT LTRIM('010101--001--001','01') FROM dual;`	`--001--001`
`SQL> SELECT LTRIM('abbba','b') FROM dual;`	`abbba`
SQL> SELECT LTRIM(LTRIM(RTRIM('abbba','a'),'a'),'b') FROM dual;	`NULL`

11.13.5 REPLACE

REPLACE(str1, str2 [,str3])

`str1`	`varchar2`	The string to be modified.
`str2`	`integer`	All occurrances of STR2 are replaced with STR3. The string STR2 is assumed to exist at least once in STR1.
`str3`	`varchar2`	This is the substitution string. It can be larger or smaller than STR2. If STR3 is not provided, then all occurrences of STR2 are replaced with NULL.
`returns`	`varchar2`	This could be the original string if no replacement occurred, a modified string, or possibly NULL depending on the substitution.

When writing code that uses REPLACE, also look at the TRANSLATE functions. They each perform string/character substitution. The REPLACE function looks for exact occurrences of STR2. Each exact occurrence is replaced with STR2.

The following replaces the string ABC with XYZ. A letter A, B, or C by itself is unchanged.

SQL> SELECT REPLACE ('a b c ab bc abc','abc','xyz') FROM dual; REPLACE('ABCABB --------------- a b c ab bc xyz

The TRANSLATE performs a character mapping ”a different type of substitution. The following replaces each letter A with X, each B with Y, and each C with Z.

SQL> SELECT TRANSLATE('a b c ab bc','abc','xyz') FROM dual; TRANSLATE(' ----------- x y z xy yz

REPLACE can be used to scrub data. To remove all occurrences of a comma followed by a line feed:

REPLACE(str, ','CHR(10))

PL/SQL procedures built using the REPLACE function provide powerful data scrubbing techniques. Suppose we want to replace all occurrences of repeated string with a single occurrence. The following procedure takes a base string, and a substring of which repeated occurrences must be replaced with a single occurrence. The result is an IN OUT mode parameter that is the scrubbed string.

CREATE OR REPLACE PROCEDURE remove_substring (base_string IN VARCHAR2, the_unwanted IN VARCHAR2, scrubbed_string IN OUT VARCHAR2) IS duplicate VARCHAR2(30) := the_unwantedthe_unwanted; BEGIN scrubbed_string := base_string; WHILE (REPLACE(scrubbed_string, duplicate, the_unwanted) <> scrubbed_string) LOOP scrubbed_string := REPLACE(scrubbed_string, duplicate, the_unwanted); END LOOP; END remove_substring;

This function can be called consecutively each time replacing the BASE_STRING with the result in SCRUBBED_STRING. This PL/SQL block uses REMOVE_SUBSTRING to first replace repeated occurrences of 01 with a single occurrence, then the same for XY.

DECLARE base_string VARCHAR2(60) := '01010101-XY-XYXYXYXY'; clean_this_1 VARCHAR2(2) := '01'; clean_this_2 VARCHAR2(2) := 'XY'; scrubbed_string VARCHAR2(60); BEGIN remove_substring(base_string, clean_this_1, scrubbed_string); remove_substring(scrubbed_string, clean_this_2, scrubbed_string); dbms_output.put_line(scrubbed_string); END;

The final string is: 01-XY-XY

11.13.6 TRANSLATE

TRANSLATE(str1, str2, str3)

`str1`	`varchar2`	The string to be modified.
`str2`	`integer`	The first character of STR2 is replaced with the first character of STR3.
`str3`	`varchar2`	This is the substitution string. It can be larger or smaller than STR2. If STR3 is not provided, then all occurrences of STR2 are replaced with NULL.
`returns`	`varchar2`	This could be the original string if no replacement occurred, a modified string or possibly NULL depending on the substitution.

The TRANSLATE function maps characters from one string onto another string. This is slightly different from REPLACE. To perform the following character mapping:

1 -> overwrites each -> A 2 -> overwrites each -> B 3 -> overwrites each -> C TRANSLATE (string, 'ABC', '123');

Using TRANSLATE on a sample string.

SQL> SELECT TRANSLATE ('ABC Axy Bxy Cxy CBA','ABC','123') 2 FROM dual; TRANSLATE('ABCAXYBX ------------------- 123 1xy 2xy 3xy 321

The following overwrites each digit with a zero.

TRANSLATE(str,'123456789','000000000');

The following translates each digit to a zero, and then removes each zero. This essentially removes all digits.

REPLACE(TRANSLATE(str,'123456789','000000000'),'0');

The following function returns TRUE if the string contains any digits.

CREATE OR REPLACE FUNCTION has_digits (str VARCHAR2) RETURN BOOLEAN IS one_to_nine CONSTANT VARCHAR2(9) := '123456789'; zeroes CONSTANT VARCHAR2(9) := '000000000'; BEGIN -- convert all digits to a 0. -- INSTR returns number > 0 of string has a 0. RETURN INSTR(TRANSLATE(str,one_to_nine,zeroes),'0') > 0; END has_digits;