Re: Changing two letters in a column, is there an easier SQL way?
define c1 smallint
define c2 smallint
define chg smallint
inside your select foreach:
let chg = false
for c1 = 1 to 29
let c2 = c1 + 1
if gear[c1,c2] = "SB" then
let gear[c1,c2] = "SD"
let chg = true
# Here, if you want to change only the first occurance, you'd
# do an "exit for"
end if
end for
if chg then
update table blah blah blah
end if
end foreach
Joey Duhon (duhon@netcom.com) wrote:
{ In article <3p0f42$dkb@miso.wwa.com>, pinoy_ako <john@miso.wwa.com> wrote:
{ >> We have Informix SE and 4gl version 6.00. We have the following table
{ ...
{ >> gear char(30)
{ ...
{ >> We have to change two letters in the gear field specifically the
{ >> letters "SB" to "SD". The problem is that the position of the
{ >> letters are different. These are some of the possible formats:
{ i would use a 4gl cursor to find the rows, and update the rows individually.
{ >> As you can see, the "SB" is at different positions. So is there a
{ >> way to do this using SQL? (dbaccess) Pls. post or email. Thanks!
{ there is a huge brute-force way, but it would be ugly:
{ update table set gear[1,2]="SD" where gear[1,2]="SB";
{ update table set gear[2,3]="SD" where gear[2,3]="SB";
{ ...
--
gburnore@garys.arasmith.com
-------------------------------------------------------------------------------
<-- Invisable Sig
-------------------------------------------------------------------------------
| '۳''''''۳'''''''''''''
Gary L. Burnore | '۳''''''۳'''''''''''''
Director, Programming & Development | '۳''''''۳'''''''''''''
Myers Information Systems, Inc. | '۳ 3 4 1 4 2 '' 6 9 0 6 9 '''
| Official Proof of Purchase
===============================================================================