Re: column formatting in Oracle

In article <37bkk8$b8t_at_bingnet1.cc.binghamton.edu>, bpanko_at_bingsuns.cc.binghamton.edu () wrote:

> 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.
>
> /*with C programming I could get this done, but importing something
> into only one column, is it possible?*/
>
> I may be missing some easy fixes.
>
> Any suggestions are most welcome.
>
> Internet address is:bpanko_at_bingsuns.cc.binghamton.edu

i'm a little confused. are you trying to do this with sql*loader? what do you mean by "import"?

if the data is in a table, you coul do something like:

update t set c =
  'Campus PO Box '||
  translate(upper(c),'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ. ','0123456789');

You might want to put a WHERE clause on this depending on distribution of data etc.

btw, i don't know exactly what you are doing here, but it may not be appropriate to include the same constant string on all values in this column.

if you are using sql*loader, i think there is a way to apply sql functions to the incoming data, but i haven't actually used it. Received on Mon Oct 10 1994 - 19:12:29 CET