Prev: Collect function
Next: troubleshooting Change Notification Registrations?
From: Sashi on 15 Oct 2009 11:21
Hi all, I'm trying to do something like this.

update myTable
case length(duration)
when 8 then set hours = substr(duration,1,2)
else set hours = substr(duration,1, 1)
end case

Depending on the length of the 'duration' field, I'm trying to extract
either the first or the first two characters.
It ain't workin'.

Is such a construct possible withing pl/sql? The few examples that
I've googled around give easy options for using it within a select but
no examples within an update.

Thanks,
Sashi
From: Maxim Demenko on 15 Oct 2009 11:28
Sashi wrote:
> Hi all, I'm trying to do something like this.
>
> update myTable
> case length(duration)
> when 8 then set hours = substr(duration,1,2)
> else set hours = substr(duration,1, 1)
> end case
>
> Depending on the length of the 'duration' field, I'm trying to extract
> either the first or the first two characters.
> It ain't workin'.
>
> Is such a construct possible withing pl/sql? The few examples that
> I've googled around give easy options for using it within a select but
> no examples within an update.
>
> Thanks,
> Sashi

update mytable set hours=case when length(duration)=8 then
substr(duration,1,2) else substr(duration,1,1) end

or shorter

update mytable set
hours=decode(length(duration),8,substr(duration,1,2),substr(duration,1,1))

(assuming you wish update all rows in your table)
Best regards

Maxim
From: Sasikanth Malladi on 15 Oct 2009 22:48
On Oct 15, 11:28 am, Maxim Demenko <mdeme...(a)gmail.com> wrote:
>
> update mytable set hours=case when length(duration)=8 then
> substr(duration,1,2) else substr(duration,1,1) end
>
> or shorter
>
> update mytable set
> hours=decode(length(duration),8,substr(duration,1,2),substr(duration,1,1))
>
> (assuming you wish update all rows in your table)
> Best regards
>
> Maxim

Great! Thank you!
Sashi
Sashi
 | 
Pages: 1
Prev: Collect function
Next: troubleshooting Change Notification Registrations?