Re: SQL UPDATE WHERE question

[Vernon Hamberg <vhamberg@xxxxxxxxxxx>]

Mark

If you are fairly current on the OS level and there will be no other 
underscores, there is a replace() function now.

Update  filename set field = replace(field, '_', '#') where 
substr(field, 1, 1) = '_'

Not sure of the order of the parameters - and maybe the "field" can 
be an expression, like a substring - don't know.

Otherwise, there is the old way -
update filename set field = '#' || substr(field, 2) where 
substr(field, 1, 1) = '_'

HTH
Vern

At 07:57 AM 9/28/2007, you wrote:

> Is there a way to update a substring of one column?
>
> I would like to replace the first character of a 10-character field with
> say "#" where ever that first byte is say "_" in a column.
>
> Can I do this with an UPDATE ... WHERE ...?
>
> Thanks.
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.