Re: Forms3 Delete a char of the field data

In article <1992Aug12.162132.278_at_falcon.navsses.navy.mil>, huynh_at_falcon.navsses.navy.mil writes:
|> Greetings,
|>
|> I am using sqlforms version 3.0.
|> I have a problem which I don't even know where to start. I want to delete a
|> char of the enter data into a field.
|> eg. Let 's say I have a field that requires data
|> HULL: _________; where the users will enter something like HULL: LST-1183,
|> I want to delete the "-" sign/char.
|> I want the data to be in the form of 'LST1183', but because of the way our data
|> bases are set up, the users will enter the HULL in the form of 'LST-1183'. I
|> want to delete the "-" sign/char, and also at the time somehow join the
|> LST 1183 together so that it is like the user had just entered 'LST1183' and
|> not 'LST-1183'.
|> I need to convert 'LST-1183' into 'LST1183', so that it corresponds with the
|> database HULL to do the query.
|>
|> I hope I have made myself clear. Anyone who is interested, but didn't quite
|> undertand me, please feel free to ask. I will be more than happy to make
|> myself clear.
|>
|> Any comments or suggestions will be appreciated. Thanks in advance.

How far along are you on this project? There appears to be a basic error in design in that the HULL field is not atomic, but includes two different pieces of information; the class of ship and its hull number. These should be separate fields. This will make queries looking for all LST's, DDG's, FF's, etc. easier. You can concatenate the two fields on output or to store the class/hull number combination in another database field.

If your database design is immutable and you need to strip out the '-' character, the following trigger will do the trick.

 BEGIN                                                                       
    IF (INSTR(:NAVY.HULL,'-') > 0) THEN                                 
      :NAVY.HULL := SUBSTR(:NAVY.HULL,1,                          
      INSTR(:NAVY.HULL,'-') -1)||                                       
      SUBSTR(:NAVY.HULL,INSTR(:NAVY.HULL,'-')+1,                  
      LENGTH(:NAVY.HULL));                                              
    END IF;                                                                  

This code should be placed in an on-validate-trigger on the class_hull field and on a pre-query trigger on the block.

Note: the use of this code will result in the '-' disappearing from the screen field as well.

                            Ian MacGregor
                            Stanford Linear Accelerator Center
                            IAN_at_SLAC.STANFORD.EDU
                            (415) 926-3528