Re: Help with new starter question

<oohrogerpalmer_at_gmail.com> wrote in message news:1149068170.020492.17090_at_i39g2000cwa.googlegroups.com...
: Hello,
:
: Can some advise me how I can move forward with this problem?
:
: I have a large table, with a certain field containing a string. I need
: to check the string for certain key words, and if I find them, insert a
: value into a different field in the same table.
:
: For example, before would be:
:
: FieldA North South East West
: East, North, West
: South, West
:
: afterwards the same table would look like:
:
: FieldA North South East West
: East, North, West Y Y Y
: South, West Y Y
:
: Hope thats clear - simple really.
:
:
: I have tried the instr check and this works fine. The only problem is
: that the string in FieldA can contain up to 20 required strings (poor
: database design, I know), and the table contains about a million rows.
:
: (Finally) my question is this - please can someone advise me the
: quickest way to go about this? As I said, I am fairly new to Oracle,
: and don't know which path I should take? Would some PL/SQL code be
: quicker than 20 instr checks and insert statements?
:
: Please someone point me in the right direction.....
:
: Thanks,
: RP
:

you don't need to store derived data (and generall should avoid doing so)

so there should be no inserts or updates involved

you can use something like this (stand-alone or as a basis for a CREATE VIEW statement) to extract the derived data (note the upper, concatenation, and replace to allow for possible inconsistencies in your denormalized field) as well as an in-line view so the formatting expression does not need to be repeated.

select
  fielda

 ,case when fielda_fmt like '%,NORTH,%' then 'N' end as north
 ,case when fielda_fmt like '%,SOUTH,%' then 'S' end as south
 ,case when fielda_fmt like '%,EAST,%'  then 'E' end as east
 ,case when fielda_fmt like '%,WEST,%'  then 'W' end as west

   select
    fielda,
    ','||upper(replace(fielda,' ',''))||',' as fielda_fmt    from sample
)

++ mcs Received on Wed May 31 2006 - 05:53:32 CDT