Re: [Q] Field formatting. How do you split one field to four.

> msoppitt_at_eastman.co.uk writes:
> Forms 4.5
> I have four fields, value 99 each and go to make one database field of
> ie 01||03 ||01||05 each set of two figures represent a description
> from an Lov (ie 4 diferent lovs make a discription that = A,B,C,D).
> How do i read the 8 numeric field from the database (ie on query) and
> split it into the four fields.
> I tried using a hidden box over the four 99 fields which would display
> for query mode and hide for insert but this is a bit messy.
>
> Any suggestions would be more than appreciated.
> Thanks, Michael
>
>

>>>>
It's best to handle components of fields which represent different information separately in a relational database. This is because, unlike traditional file-oriented languages, the relational model does not accommodate subfields. When I design a database, I always question fields which are a mixture of letters and numbers; they usually turn out to have special codes embedded in them, and the users always turn out to need to select and sort data according to those codes. Putting each component of such a complex field into its own column does everybody a favor. If users insist, you can concatenate the columns to look like the original field on retrieval and they will never know the difference.

If you have built the database and are stuck with a column that has embedded components, you may be able to use sql functions to get at them. Look in the front of the SQL reference manual for substring functions for character fields. If it is a numeric field, use the TO_CHAR() function inside the substring operations to translate it before they get it.

• Paul de Anguera, City of Seattle / HRIS