Working with Strings（使用Oracle字符串）(四)
ovide the string, the position at which the desired substring starts, and the number of characters in the substring.
Listing 2 shows some examples that use the SUBSTR function.
Code Listing 2: Examples of SUBSTR function
DECLARE
l_company_name VARCHAR2 (6) := 'Oracle';
BEGIN
/* Retrieve the first character in the string */
DBMS_OUTPUT.put_line (
SUBSTR (l_company_name, 1, 1));
/* Retrieve the last character in the string */
DBMS_OUTPUT.put_line (
SUBSTR (l_company_name, -1, 1));
/* Retrieve three characters,
starting from the second position. */
DBMS_OUTPUT.put_line (
SUBSTR (l_company_name, 2, 3));
/* Retrieve the remainder of the string,
starting from the second position. */
DBMS_OUTPUT.put_line (
SUBSTR (l_company_name, 2));
END;
/
The output from this block is:
O
e
rac
racle
As you can see, with the SUBSTR function you can specify a negative starting position for the substring, in which case Oracle Database counts backward from the end of the string. If you do not provide a third argument—the number of characters in the substring—Oracle Database automatically returns the remainder of the string from the specified position.
Find a string within another string. Use the INSTR function to determine where (and if) a string appears within another string. INSTR accepts as many as four arguments:
The string to be searched (required).
The substring of interest (required).
The starting position of the search (optional). If the value is negative, count from the end of the string. If no value is provided, Oracle Database starts at the beginning of the string; that is, the starting position is 1.
The Nth occurrence of the substring (optional). If no value is provided, Oracle Database looks for the first occurrence.
Listing 3 shows some examples that use the INSTR function.
Code Listing 3: Examples of INSTR function
BEGIN
/* Find the location of the first "e" */
DBMS_OUTPUT.put_line (
INSTR ('steven feuerstein', 'e'));
/* Find the location of the first "e" starting from position 6 */
DBMS_OUTPUT.put_line (
INSTR ('steven feuerstein'
, 'e'
, 6));
/* Find the location of the first "e" starting from the 6th position from
the end of string and counting to the left. */
DBMS_OUTPUT.put_line (
INSTR ('steven feuerstein'
, 'e'
, -6));
/* Find the location of the 3rd "e" starting from the 6th position from
the end of string. */
DBMS_OUTPUT.put_line (
INSTR ('steven feuerstein'
, 'e'
, -6
, 3));
END;
/
The output from this block is:
3
9
11
5
INSTR is a very flexible and handy utility. It can easily be used to determine whether or not a substring appears at all in a string. Here is a Boolean function that does just that:
CREATE OR REPLACE FUNCTION
is_in_string (
string_in IN VARCHAR2
,substring_in IN VARCHAR2)
RETURN BOOLEAN
IS
BEGIN
RETURN INSTR (string_in
, substring_in) > 0;
END is_in_string;
/
Pad a string with spaces (or other characters). I warned earlier about using the CHAR datatype, because Oracle Database pads your string value with spaces to the maximum length specified in the declaration.
However, there are times, prim