Re: TRIM and SUBSTR not working

From: steve <me_at_me.com>
Date: Sat, 12 Feb 2005 06:04:24 +0800
Message-ID: <0001HW.BE334CE80002D2BAF03055B0@news.newsguy.com>

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

> 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
>
> Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
>
>
> 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_at_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

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 Received on Fri Feb 11 2005 - 16:04:24 CST