View the Exhibit and examine the structure of the CUSTOMERS table.
In the CUSTOMERS table, the CUST_LAST_NAME column contains the values ‘Anderson’ and
‘Ausson’.
You issue the following query:
SQL> SELECT LOWER(REPLACE(TRIM(‘son’ FROM cust_last_name),’An’,’O’))
FROM CUSTOMERS
WHERE LOWER(cust_last_name) LIKE ‘a%n’;
What would be the outcome?
A.
‘Oder’ and ‘Aus’
B.
an error because the TRIM function specified is not valid
C.
an error because the LOWER function specified is not valid
D.
an error because the REPLACE function specified is not valid
Explanation:
Function Purpose
ROUND(column|expression, n) Rounds the column, expression, or value to n decimal places or, if
n is omitted, no decimal places (If n is negative, numbers to the left of decimal point are rounded.)
TRUNC(column|expression, n) Truncates the column, expression, or value to n decimal places or,
if n is omitted, n defaults to zero
The TRIM Function
The TRIM function removes characters from the beginning or end of character literals, columns or
expressions to yield one potentially shorter character item. Numeric and date literals are
automatically cast as characters when they occur as parameters to the TRIM function.
Numeric or date expressions are evaluated first before being converted to strings ready to be
trimmed.
The TRIM function takes a parameter made up of an optional and a mandatory component.
Its syntax is TRIM
([trailing|leading|both] trimstring from s).
The string to be trimmed (s) is mandatory. The following points list the rules governing the use of
this function:
TRIM(s) removes spaces from both sides of the input string.
TRIM(trailing trimstring from s) removes all occurrences of trimstring from the end of the string s if
it is present.
TRIM(leading trimstring from s) removes all occurrences of trimstring from the beginning of the
string s if it is present.
TRIM(both trimstring from s) removes all occurrences of trimstring from the beginning and end of
the string s if it is present.
The following queries illustrate the usage of this function:
Query 1: select trim(trailing ‘e’ from 1+2.14||’ is pie’) from dual
Query 2: select trim(both ‘*’ from ‘*******Hidden*******’) from dual
Query 3: select trim(1 from sysdate) from dual
ORA-30001: trim set should have only one character
30001. 00000 – “trim set should have only one character”
*Cause: Trim set contains more or less than 1 character. This is not allowed in TRIM function.
REPLACE(text, search_string, replacement_string)
Searches a text expression for a character string and, if found, replaces it with a specified
replacement string