Re: column formatting in Oracle
From: Stephen W. Thompson <st_at_blue.seas.upenn.edu>
Date: 20 Oct 1994 16:32:43 GMT
Message-ID: <38663b$lhe_at_netnews.upenn.edu>
: >I have a column in a table, which should be looking like this:
: >Campus PO Box 76234
: >
: >But during data entry we have put all sorts of combinations like:
: >campus po box 76234
: >Po Box 76234
: >PO BOX 76234
: >CAMPUS P.O POX 76234
: >and so on.
: >
: >Is there a way to fix this column so that the "number part" stays
: >and the beginning part looks same for all columns as described.
Date: 20 Oct 1994 16:32:43 GMT
Message-ID: <38663b$lhe_at_netnews.upenn.edu>
Chaz Sliger (chaz_at_atlas.com) wrote:
: In article <37bkk8$b8t_at_bingnet1.cc.binghamton.edu> bpanko_at_bingsuns.cc.binghamton.edu () writes:
: >I have a column in a table, which should be looking like this:
: >Campus PO Box 76234
: >
: >But during data entry we have put all sorts of combinations like:
: >campus po box 76234
: >Po Box 76234
: >PO BOX 76234
: >CAMPUS P.O POX 76234
: >and so on.
: >
: >Is there a way to fix this column so that the "number part" stays
: >and the beginning part looks same for all columns as described.
: Look at the string handling functions. : They can be used to clean this sort of stuff up. : (INSTR, INITCAP, SUBSTR, etc.)
Of course, you should address the data administration issue, also, which is if you want to continue to allow such formating madness. Perhaps you need to use a trigger on the form (of course, the data could be coming from elsewhere...) which does this cleaning up before it gets to the table. OR create a separate "Campus_Box" column. It depends on your organization as to which you want to pursue, if either.
En paz,
Steve
-- Stephen W. Thompson, U. of PA, Data Administration, 215-898-1236 thompson_at_pobox.upenn.edu -OR- st_at_eniac.seas.upenn.edu URL: http://www.seas.upenn.edu/~st/st.htmlReceived on Thu Oct 20 1994 - 17:32:43 CET