Re: How do I update part of a field's value in SQL

dthomas5_at_my-deja.com wrote:
>
> In a table I wish to update I have a field named BINNUM which refers to the
> location an item is stored in a stockroom. BINNUM is seven (7) characters
> long and is represented by the following example: MC1402A. The MC refers to
> the aisle the cabinet is located, the 14 refers to the cabinet number (the
> 14th one down the aisle in this case) 02 refers to the drawer number (second
> drawer from the top here) and A for the location within that drawer.
>
> We have just finished rearranging the stockroom, so unfortunately the
> cabinets are no longer in the same place, requiring them to be renumbered
> physically and within the application. So for example MC1402A may now be
> MD0602A (drawer number and space in drawer remain the same)---I need to be
> able to change just the first four characters to a new value so that anything
> that was MC14*** is now MD06*** for the entire contents of the cabinet. I am
> not certain how this is done as an update using a wildcard--or even if that
> is the right approach. I know that in DOS I can rename a series of files and
> maintain part of the original name using a wildcard but not sure here.

UPDATE table
SET BINNUM = 'MD06' || SUBSTR(BINNIM, 5) WHERE BINNUM LIKE 'MC14___' -- EXACTLY seven characters

Valery Yourinsky

-- 
Oracle8 Certified DBA