Re: question about updating a column

titsataki1995-google_at_yahoo.com (titsataki) wrote in message news:<69e7d2bd.0312111654.6777ed9_at_posting.google.com>...
> hi everybody,
>
> I trying to update a table named account. the column that I want to
> update is called BPSTATE. BPSTATE is a VARCHAR:
>
> BPSTATE VARCHAR2(4000)
>
> It seems that it sorts in aphabetical order.
>
> select bpstate from account
> where substr(bpstate,1,5)='"4.1"'
> and rownum < 10;
>
> the results are:
>
> BPSTATE
> --------------------------------------------------------------------------------
> "4.1" /models/Account(Active,Correlated,Approved)\\
> "4.1" /models/Account(Correlated,Approved,Inactive)\\
> "4.1" /models/Account(Active,Correlated,UnknownApproval)\\
> "4.1" /models/Account(Correlated,Inactive,UnknownApproval)\\
> "4.1" /models/Account(Active,Correlated,UnknownApproval)\\
> "4.1" /models/Account(Correlated,Inactive,UnknownApproval)\\
> "4.1" /models/Account(Active,Correlated,UnknownApproval)\\
> "4.1" /models/Account(Active,Correlated,UnknownApproval)\\
> "4.1" /models/Account(Active,Correlated,UnknownApproval)\\
>
>
> I thought I can do
>
> update account
> set SUBSTR(BPSTATE,30,10)= 'Uncorrelated'
> where in
> (select bpstate from account
> where
> substr(30,10) = 'Correlated'
> and VTOID like '1234567890';)
>
> I am a little beacuse of the sorting as I want to replace the
> Correlated with Uncorrelated and not just add it.
>
> Is there a generic SQL command that does not need the substring, but
> it finds a replaces a value with another value regadrless where it is?
> (replace Correlated with Uncorrelated). And still does not mess with
> the the rest of the info?
>
> Thanks
>
> Nick

Nick, pull out your SQL manual and look up the following functions: replace, transform, and instr. I would think you could set the column equal to a replace(column,'oldstring','newstring') but check the manual entries.

HTH -- Mark D Powell -- Received on Fri Dec 12 2003 - 08:23:35 CST