Re: Unique Field from a string

Not sure if this is an answer you are looking for but here we go.

It sounds like the eight fields you are dealing with must be unique in a table. If I understand your problem, you are not looking to have these eight column migrate to any child tables that you need to create.

You need to make sure that the eight fields are uniq so create a unique constraint on those fields. This will prevent you from entering the same eight values again. Note, you may want to place a trigger on the table to trim any blank padding from the right and left of the string(s). In the same trigger, generate a unique number by using a SEQUENCE that you can create in Oracle. Use this value to populate a numeric column that would be the primary key of the table. This, I believe, would be the easiest solution to your problem. It sounds like you have some plans on building a number generator based on a string passed to it. Why waste the time doing this when such an easy solution exists?

Dave Grzebien
Expert Technical Consultants, Inc

designpixs_at_my-deja.com wrote:

> I need to get a number field populated (for use as a primary key) using
> a combination of 8 fields. These 8 fields are varchar2 and will total
> 175 characters if all were populated to their max size. So, what I
> need to do is concatinate these 8 fields and then find a way to get a
> unique number value from it. (i.e. if I have 'THISISATEST' I would get
> 12345 or 'THISISANOTHERTEST' I would get 12321). The number has to be
> unique to the string passed in. So if I pass the same string in again
> I will get the same number but if I pass a different string in I can't
> get that number. I messed around with DBMS_UTILITY.GET_HASH_VALUE but
> that produced poor results.
>
> Hope someone can help.
>
> Thanks,
> Drew
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Fri Dec 01 2000 - 21:08:37 CST