"Andreas Mosmann" <mosmann@[EMAIL PROTECTED]
> schreef in
bericht news:1207151121.77@[EMAIL PROTECTED]
> Carlos schrieb am 02.04.2008 in
> <360af7b1-487f-4485-81d7-0d13abd40d3e@[EMAIL PROTECTED]
>:
>
>> On 2 abr, 16:54, Andreas Mosmann <mosm...@[EMAIL PROTECTED]
>> group.org> wrote:
>>> Carlos schrieb am 02.04.2008 in
>>> <221a3638-5b82-4086-b097-8e06e50bd...@[EMAIL PROTECTED]
>:
>>>
>>>> Not sure about what you're trying to do, but a Function Based Unique
>>>> Index on (trim(upper(column))) may do the trick...
>>>
>>> I try to ensure, that it is impossible to insert/update records in the
>>> way, that after this there are two records, that differ only by
>>> BIG/little - Letters and the number of spaces
>>>
>>> example:
>>>
>>> CMyColumn
>>> ----------
>>> TeSt EnTrY
>>>
>>> It should be impossible to insert a record with CMyColumn > 'TEST
ENTRY'
>>> or 'test entry' or ' test entry ' ...
>>>
>>>> HTH.
>>>
>>> I tried it out:
>>> CREATE UNIQUE INDEX MySchema.MyIndexName
>>> ON MySchema.MyTable upper(trim((CLOGINNAME)))
>>> TABLESPACE MyIndexTableSpace LOGGING;
>>> No Problem to create that index, but, after it, still no problem to
>>> insert f.e. 'TEST ENTRY'
>>>
>>> Any more idea?
>>>
>>>> Cheers.
>>> Thanks
>>>> Carlos.
>>>
>>> Andreas
>>>
>>> --
>>> wenn email, dann AndreasMosmann <bei> web <punkt> de
>
>> If you want to avoid INNER blanks, you should add some REPLACE() to
>> the index expression.
>
>> But If you have successfully created the unique index, you shouldn't
>> be able to insert 'TEST ENTRY' if you have already inserted 'test
>> Entry'.
> you are particulary right. I did
> CREATE UNIQUE INDEX MySchema.MyIndexName
> ON MySchema.MyTable upper(trim((CLOGINNAME)))
> TABLESPACE MyIndexTableSpace LOGGING;
> but in the Oracle Enterprise Manager I the index was only noted as
> trim(CLOGINNAME), the upper was not to be found.
> Now I turned the functions around and I can only find upper without
trim.
> Now it works with upper _and_ with trim, but not with upper(trim())
> that means
> ' Test ' = ' TEST '
> ' Test' = 'Test '
> but
> 'Test ' <> ' TEST'
> I could write a wrapper function MyFunction as 'trim(upper())' but it
> sounds crazy ...
>
> Now I tried
> CREATE UNIQUE INDEX MySchema.MyIndexName
> ON MySchema.MyTable trim(upper(trim(CLOGINNAME)))
> TABLESPACE MyIndexTableSpace LOGGING;
> and it works ...
>
> The outer function is ignored. Is this my mistake or an oracle bug?
>
>> Cheers.
> thank you
>> Carlos.
> Andreas Mosmann
>
>
> --
> wenn email, dann AndreasMosmann <bei> web <punkt> de
I can reproduce this behaviour on my system.
BTW did you try this? (note the brackets)
CREATE UNIQUE INDEX MySchema.MyIndexName
ON MySchema.MyTable (upper(trim(CLOGINNAME)))
TABLESPACE MyIndexTableSpace LOGGING;
Works on my 10g database:
SQL> create table mytab(myname varchar2(10));
Table created.
SQL> create unique index myindex on mytab (upper(trim(myname)))
Index created.
SQL> insert into mytab values('smallcaps');
1 row inserted.
SQL> insert into mytab values('SmAllCapS');
insert into mytab values('SmAllCapS')
*
ERROR in line 1:
..ORA-00001: Violating UNIQUE-constraint (TEST.MYINDEX).
Errors may not be accurate; translated from Dutch.
Shakespeare
|
