S TRINGING S TRINGS TOGE THER
Sometimes it makes sense to combine
certain strings, such as the FIRS T_NAME
and LAST_NAME values from the EMPLOYEE
table, in the result set display. You can use
concatenation to accomplish this task—with
either the CONCAT function, illustrated in
Listing 6, or the (more commonly used)
concatenation operator || (two pipe characters), illustrated in Listing 7.
The CONCAT function takes two parameters and concatenates them. You can also
nest multiple CONCAT function calls, as
shown in Listing 8. The queries in Listings 7
and 8 concatenate literal strings with column
data values. (I prefer the concatenation
operator, because it has unlimited input
parameters and makes the concatenated
output more readable.)
Code Listing 11: Query that demonstrates the INSTR character function
SQL> select last_name, INSTR(last_name, 'ton') ton_starting_point
2 from employee
3 order by last_name;
LAST_NAME TON_STARTING_POINT
————————————————————————————————————————————— ———————————————————————————
Eckhardt 0
Friedli 0
James 0
Michaels 0
Newton 4
Newton 4
leblanc 0
peterson 0
8 rows selected.
Code Listing 12: Query that demonstrates the SUBSTR character function
SQL> select last_name, SUBSTR(last_name, 1, 3)
2 from employee
3 order by last_name;
GIVING YOUR DATA A TRIM
Sometimes you want to remove unwanted
spaces or characters from data when you
display it. For example, data inserted into a
table column via a form application might
include extraneous characters or spaces—
preceding and/or following the actual data
value—that the form input field doesn’t trim.
Listing 9 shows a query that trims extra
spaces from string values. The TRIM function in Listing 9 takes two parameters. The
first parameter is the character, symbol,
or space (delimited by single quotes) to
be removed. The second parameter specifies the string literal or column value to be
trimmed. The TRIM function supports three
keywords: LEADING, TRAILING, and BOTH.
The example in Listing 9 uses the TRAILING
keyword to right-trim the FIRST_NAME
value. The TRIM function applied to the
LAS T_NAME value specifies the LEADING
keyword to left-trim the spaces from that
value. And, as you can see, the spaces to the
right of the LAST_NAME value remain and
are included in the output.
Compare the output in Listing 9 with
that in Listing 10, which trims the rightmost
extra spaces from the LAST_NAME value.
When no keyword is specified, the default
behavior for the TRIM function is to trim
leading as well as trailing characters. The
older RTRIM and LTRIM functions are available for backward compatibility.
LAST_NAME SUB
———————————————————————————————————————————— ————
Eckhardt Eck
Friedli Fri
James Jam
Michaels Mic
Newton New
Newton New
leblanc leb
peterson pet
8 rows selected.
Code Listing 13: Query that demonstrates the INSTR and SUBSTR character functions
SQL> select last_name, INSTR(last_name, 'ton') ton_position, SUBSTR(last_name,
INSTR(last_name, 'ton')) substring_ton
2 from employee
3 order by last_name;
LAST_NAME TON_POSITION SUBSTRING_TON
———————————————————————————————————————————— ————————————————— ——————————————————————————————
Eckhardt 0 Eckhardt
Friedli 0 Friedli
James 0 James
Michaels 0 Michaels
Newton 4 ton
Newton 4 ton
leblanc 0 leblanc
peterson 0 peterson
8 rows selected.
SEARCHING FOR S TRINGS WI THIN S TRINGS
When you need to search column values for
similar string pattern values, you can do so
with the INSTR character function. INS TR—
which stands for in string—returns the
position of a substring within a string value.
Listing 11 demonstrates the INS TR function
applied to the LAST_NAME column of the
EMPLOYEE table to locate all occurrences of
the “ton” substring. As you can see, the INSTR
