Mass change of part of a field in data table

[blodwyn]

Hi

There is a field in a large data table which contains users email addresses, eg xxxx@mybusiness.com.

I need to be able to change all these addresses to xxxx@yourbusiness.com.

Although xxxx relates to the individual users, it does not exist itself in the table.

Is there a way I can update just the ISP details in the field?

Blodwyn

 

[olded]

Hi:

I'm assuming since you are asking this question in the 4GL forum, that I can use 4GL. In this case, I think 4GL is a good choice since I'm not aware of a way to do this in SQL.

Generally, I'd set up a cursor to read each record, parse the email column for the ISP, replace the ISP, and update the record.

I'll leave the database stuff to you, but the following program demos the parsing. Years ago, I wrote parse_str which, in your case, a since call should return "xxxx@" and the length of that string.

Let me know if you have any questions.

MAIN
DEFINE 
   dummy_string CHAR(20),
   new_string   CHAR(20),
   tmp_str      CHAR(80),
   new_isp      CHAR(8),
   cnt          SMALLINT
 
   LET cnt = 0
   LET tmp_str = "xxxx@mybusiness.com"
   LET new_isp = "yourbusiness.com"
 
   CALL parse_str("@", cnt, tmp_str) RETURNING
      dummy_string, cnt 
 
   LET new_string = tmp_str[1, cnt]
   LET new_string = tmp_str, new_isp CLIPPED
 
END MAIN
 
FUNCTION parse_str(char_delim, cnt, string)
# This function searches string for an instance of char_delim 
# starting at position cnt and returns the string ending at the next
# char_delim or the end of string
#
DEFINE 
   char_delim char(1),   # character delimiter
   cnt smallint,         # postion in string to start counting
   string char(300),     # string to search
   ret_string char(40),  # string returned
   i,                    # index
   x,                    # counter
   string_len smallint   # string length
 
   LET string_len = LENGTH(string)
 
   IF string_len = 0 # got a null string
   THEN 
     RETURN "",0
   END IF
 
      LET cnt = cnt + 1 #get off the delimiter
 
   IF cnt > string_len
   THEN # return when the end of the string is reached
     RETURN "",0
   END IF
   # check the string for 
   LET x = 1
   FOR i = cnt TO string_len
      IF string[i] = char_delim
      THEN
         EXIT FOR
      END IF
      LET ret_string[x] = string[i]  # save a character 
      LET x = x + 1
   END FOR
 
   LET cnt = i # return the positon ended
   RETURN ret_string, cnt
END FUNCTION

 

[PHV]

I'd replace this:
LET new_string = tmp_str[1, cnt]
LET new_string = tmp_str, new_isp CLIPPED
By this:
LET new_string = tmp_str[1, cnt], new_isp CLIPPED

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886