Thanks for the question, Anastasia.
Asked: July 21, 2016 - 11:45 am UTC
Last updated: July 21, 2016 - 12:54 pm UTC
Version: 12c
Viewed 1000+ times
You Asked
Hello, Tom!
I've a lack of undestanding of 'trim' fuction's IN parameters.
It's not explicitly said in the docs, that only a single character can be used as trim_character.
However, an attempt to use a syntax like this:
SELECT TRIM(BOTH 'Aa' from 'Ana') FROM DUAL;
produces the error:
"ORA-30001: trim set should have only one character"
I might be confused with my study-materials.
They say:
"function returns c3 with all c1 (leading, trailing, orboth) occurrences of characters IN c2 removed" --{TRIM([c1],[c2], c3)}--
Docs.oracle.com, in turn, says:
"Oracle removes ANY {trailing\leading} characters equal to trim_character"
I'm working with Oracle 11g now, and I'm wondering if the TRIM function works the same way in Oracle 12c.
Thanks in advance for the answer.
and Chris said...
Yes, you can only have one character defined to trim.
The docs state "trim_character". So they are consistent with this behaviour.
Of course, trim can remove multiple characters from the input:
SELECT TRIM('a' from 'AAAAnaaa'),
TRIM('A' from 'AAAAnaaa')
FROM DUAL;
TRIM('A'FROM'AAAANAAA') TRIM('A'FROM'AAAANAAA')
AAAAn naaa
12c behaves the same:
SQL> select * from v$version;
BANNER CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c EE Extreme Perf Release 12.1.0.2.0 - 64bit Production 0
PL/SQL Release 12.1.0.2.0 - Production 0
CORE 12.1.0.2.0 Production 0
TNS for Linux: Version 12.1.0.2.0 - Production 0
NLSRTL Version 12.1.0.2.0 - Production 0
SQL> SELECT TRIM('Aa' from 'AAAAnaaa')
2 FROM DUAL;
SELECT TRIM('Aa' from 'AAAAnaaa')
*
ERROR at line 1:
ORA-30001: trim set should have only one character
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment