Re: 9i: Check- constraint Upper(Column)

From: Andreas Mosmann <mosmann_at_expires-30-04-2008.news-group.org>
Date: Wed, 02 Apr 2008 17:45:21 +0200
Message-ID: <1207151121.77@user.newsoffice.de>

Carlos schrieb am 02.04.2008 in
<360af7b1-487f-4485-81d7-0d13abd40d3e_at_r9g2000prd.googlegroups.com>:

> On 2 abr, 16:54, Andreas Mosmann <mosm..._at_expires-30-04-2008.news-
> group.org> wrote:

>> Carlos schrieb am 02.04.2008 in
>> <221a3638-5b82-4086-b097-8e06e50bd..._at_x41g2000hsb.googlegroups.com>:
>>

>>> 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

Received on Wed Apr 02 2008 - 10:45:21 CDT