Hi, Mario - 

Could you be a little more detailed or provide an example? Are you referring to replacing "international"-type characters with "standard"-type characters (replacing "Ä" with "A", for example)? Is there a known set of characters to be replaced? A clearer definition of the issue you are facing will help us suggest possible solutions.

Thanks for your reply John.

I mean all the special characters that exist besides the ones you mention.
Tab, end of line, enter, .,!|°, etc.

In other words, we need to leave ONLY numbers in that field already in the informix database.

Or that we can perform a select of the data and that it gives us the varchar type field as a result, "transformed" into just numbers.

For example, some data that we have would be

97814 14545454
878457!45487
4656553|4343
668794LC123124
7675745LETRA423456

5464636+45353425

564654367.754754

Thanks and regards.

I've no idea how to accomplish this within the confines of an Informix database (you do realize this is a PowerBuilder and related products Q&A forum, right?)

Using PowerBuilder, one possibility would be to use a filter expression in a DataWindow/DataStore. If you use a filter expression that contains the "Match" DataWindow expression function, such as:

    Match(columnname,'[^0-9]')

Any row where that column contains only digits will be filtered (removed from view), leaving only the row(s) that have one or more non-numeric characters.

You can easily write a PowerScript function that examines each character of a string value and discards any character that is not a digit. Note: What will you do if the string contains NO digits?

Then simply loop through all of the non-filtered rows (i.e., all of the rows in the DW/DS's primary buffer), use your "clean up" function to assign a sanitized value to the column in each row, then dw.update().

Of course, you need to be smart about this... If you have a large number of rows to be examined, you'll probably want to retrieve them in reasonable-sized chunks (for example, 10,000-25,000 rows at a time). I suggest you retrieve only the primary key columns and the column(s) to be cleaned instead of the entire row, particularly if the table contains a lot of columns.

Once the data has been cleaned, you might wish to consider adding some input validation to prevent dirty data from being entered by the users in the future.

I hope this gives you some ideas. Good luck!

John

Mario, i think, this is what you need:

long l_row, l_len, l_pos, l_asc
string s_data, s_return

for l_row = 1 to dw_1.rowcount()
     s_data = dw_1.getitemstring(l_row, 'dirty_column_name')

     s_return = Trim(s_data)

     l_len = Len(s_return)

     FOR l_pos = 1 TO l_len
            l_asc = Asc(Mid(s_return, l_pos, 1))

            IF l_asc < 48 or l_asc > 57 THEN
                s_return = Replace(s_return, l_pos, 1, '')
            END IF
     NEXT

      s_return = Trim(s_return)

      IF Len(s_return) > 0 THEN
          if isnumber(s_return) then
             dw_1.setitem(l_row, 'clean_column_name', s_return)
          end if
      END IF
next