O'Reilly logo

Oracle PL/SQL Programming: A Developer's Workbook by Andrew Odewahn, Steven Feuerstein

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Chapter 18. Character Functions

Beginner

Q:

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.

Q:

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, ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required