TRIM and SUBSTR not working

Matthias Wirtz

unread,

Feb 9, 2005, 7:49:01 PM2/9/05

to

Hi,

I jus wonder why TRIM and SUBSTR are not working on one Oracle9i database
server:

SQL*Plus: Release 9.2.0.1.0 - Production on Thu Feb 10 11:47:01 2005

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

stat@STAT2> select TRIM('Hello ') o from dual;

O
---------------------------
Hello

You see that the string is not trimmed. I would expected (and actually it is
on a different box) the output like this:

O
-----
Hello

Same happen with SUBSTR, doesn't work. But why is this different? Are there
any default sql*plus settings that I'm not aware off?

--
Matthias Wirtz - Norfolk, usa

Turkbear

unread,

Feb 10, 2005, 12:10:49 PM2/10/05

to

Matthias Wirtz <Matthia...@gmx.net> wrote:

I cannot reproduce the problem..
--------------------------------------------------------------------------------------------------------------
SQL*Plus: Release 9.2.0.1.0 - Production on Thu Feb 10 11:08:07 2005

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.2.1 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.2.0 - Production

SQL> select trim('Hello ') from dual;

TRIM(
-----
Hello

SQL> select substr('thislongstring',2,4) from dual;

SUBS
----
hisl

SQL>
-------------------------------------------------------------------------------------------------

No setting in SqlPlus have been modified..'out-of-the-box' client install.

Anurag Varma

unread,

Feb 10, 2005, 12:26:06 PM2/10/05

to

I think this has to do with the database characterset.
If its a single byte .. you see the latter behavior.
If its multibyte .. then you see the former behavior.

HTH

Anurag

Matthias Wirtz

unread,

Feb 9, 2005, 10:39:09 PM2/9/05

to

Anurag Varma wrote:

Both databases have the same character sets:

stat@STAT2> select * from v$nls_parameters where parameter LIKE
'NLS%_CHARACTERSET';

PARAMETER VALUE
-------------------------- -------------
NLS_CHARACTERSET WE8ISO8859P1
NLS_NCHAR_CHARACTERSET AL16UTF16

Michael Krzepkowski

unread,

Feb 10, 2005, 3:55:22 PM2/10/05

to

Hi,

Look at the select you have used "trim(bla) o " (label!)
vs what others run "trim(bla)"

However when you try
> select trim(bla) a,length(trim(bla)) from blabla

you should get correct results. Just the presentation of the column
heading is incorrect.

HTH

Michael

Anurag Varma

unread,

Feb 10, 2005, 4:18:27 PM2/10/05

to

ah ... then I recall incorrectly.
How about cursor sharing?

ORA92> select trim('Hello ') from dual;

TRIM('HELLO')
--------------------------------
Hello

ORA92> show parameter cursor_sharing

NAME TYPE
VALUE
------------------------------------ --------------------------------
------------------------------
cursor_sharing string
SIMILAR
ORA92> alter session set cursor_sharing=exact;

Session altered.

ORA92> select trim('Hello ') from dual;

TRIM(
-----
Hello

Anurag

Anurag Varma

unread,

Feb 10, 2005, 4:23:34 PM2/10/05

to

good point. Although I was hoping that OP is aware of that. If he wants
to properly format
the columns, he should explicitly specify the format.

Anurag

steve

unread,

Feb 11, 2005, 5:04:24 PM2/11/05

to

On Thu, 10 Feb 2005 08:49:01 +0800, Matthias Wirtz wrote
(in article <371iaiF...@individual.net>):

don't be silly.
consider for 10 seconds , the impact of your statement.
.........
do you think that the whole of your oracle database internal code , does not
use " trim & substr" in any form.

Why is your database still running?
Why has it not crashed beyond a completely irreparable state?

SQL> select length('im a newbie who should not be let near a real database')
from dual;

LENGTH('IMANEWBIEWHOSHOULDNOTBELETNEARAREALDATABASE')
-----------------------------------------------------
54
SQL> select length('im a newbie who should not be let near a real database
') from dual;

LENGTH('IMANEWBIEWHOSHOULDNOTBELETNEARAREALDATABASE')
-----------------------------------------------------
65

select length(trim('im a newbie who should not be let near a real database
'))from dual;

LENGTH(TRIM('IMANEWBIEWHOSHOULDNOTBELETNEARAREALDATABASE'))
-----------------------------------------------------------
54

SQL> select 'stay away from '||substr('im a newbie who should not be let near
a real database',39) from dual;

'STAYAWAYFROM'||SUBSTR('IMANEWB
-------------------------------
stay away from a real database

As part of your training , see if you can manage to cut & paste the above sql
into your sqlplus program , as a test ( pls note, no need for scissors or
glue, but if you feel better you can use the plastic scissors)

steve

Matthias Wirtz

unread,

Feb 13, 2005, 4:24:42 AM2/13/05

to

Anurag Varma wrote:

> How about cursor sharing?

Yes, that worked. I changed it to exact, like in your example and got the
expected output. Thx.