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