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
|
