Skip to Main Content
Oracle PL/SQL Programming: A Developer's Workbook
book

Oracle PL/SQL Programming: A Developer's Workbook

by Steven Feuerstein, Andrew Odewahn
May 2000
Intermediate to advanced content levelIntermediate to advanced
594 pages
11h 32m
English
O'Reilly Media, Inc.
Content preview from Oracle PL/SQL Programming: A Developer's Workbook

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

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Oracle Database 12c PL/SQL Programming

Oracle Database 12c PL/SQL Programming

Michael McLaughlin
Oracle PL/SQL for DBAs

Oracle PL/SQL for DBAs

Arup Nanda, Steven Feuerstein
Oracle PL/SQL For Dummies

Oracle PL/SQL For Dummies

Michael Rosenblum, Paul Dorsey

Publisher Resources

ISBN: 9781449324070Supplemental ContentErrata Page