How to remove all leading and trailing white space characters from all the CHARACTER fields of all the user data tables of a database?

How to remove all instances of spaces, tabs, line feeds, and carriage returns from all the CHARACTER fields of all the user data tables of a database?

How to reference the BUFFER-VALUE of individual elements of an array field?

The sample code attached to this knowledge base article demonstrates how to dynamically remove all leading and trailing white space characters from all the CHARACTER fields of all the user data tables of  the connected database.  The code also demonstrates how to dynamically access the BUFFER-VALUE of individual elements of an array field.

The following 4GL/ABL procedure, which is also attached to this article, uses the 4GL/ABL TRIM function to remove all leading and trailing white space characters from all the CHARACTER fields of all the user data tables of the connected database:
 

/***************Define variables************/
DEFINE VARIABLE hBufferHandle AS HANDLE     NO-UNDO.
DEFINE VARIABLE cTableName    AS CHARACTER  NO-UNDO.
DEFINE VARIABLE iFieldCounter      AS INTEGER    NO-UNDO.
DEFINE VARIABLE iExtentCounter      AS INTEGER    NO-UNDO.
DEFINE VARIABLE hFieldHandle  AS HANDLE     NO-UNDO.
DEFINE VARIABLE hQueryHandle  AS HANDLE     NO-UNDO.

/********Loop through all tables********/
FOR EACH _File NO-LOCK WHERE _Tbl-Type = "T":
    ASSIGN
       cTableName     = _File-Name.

    /* Create dynamic query for the current table */
    CREATE BUFFER  hBufferHandle FOR TABLE cTableName.
    CREATE QUERY  hQueryHandle.
    hQueryHandle:SET-BUFFERS(hBufferHandle).
    hQueryHandle:QUERY-PREPARE("for each " + cTableName + " NO-LOCK").
    hQueryHandle:QUERY-OPEN.
    
    /*  Remove leading and trailing white space characters from all CHARACTER fields */
    REPEAT:
        hQueryHandle:GET-NEXT().
        IF hQueryHandle:QUERY-OFF-END THEN LEAVE.
        DO iFieldCounter = 1 TO hBufferHandle:NUM-FIELDS:
            hFieldHandle = hBufferHandle:BUFFER-FIELD(iFieldCounter).
            IF (hFieldHandle:DATA-TYPE <> "CHARACTER") THEN NEXT.
            IF  hFieldHandle:EXTENT = 0 THEN /* Field is NOT an Array */
                DO TRANSACTION:
                    hQueryHandle:GET-CURRENT(EXCLUSIVE-LOCK).
                    hFieldHandle:BUFFER-VALUE = TRIM(hFieldHandle:BUFFER-VALUE).
                END.  /* DO TRANSACTION */
            ELSE  /* Field is an Array */
                DO TRANSACTION:
                    hQueryHandle:GET-CURRENT(EXCLUSIVE-LOCK).
                    DO iExtentCounter = 1 TO hFieldHandle:EXTENT:
                        hFieldHandle:BUFFER-VALUE[iExtentCounter] = TRIM(hFieldHandle:BUFFER-VALUE[iExtentCounter]).
                    END.   /* DO iExtentCounter */
            END.  /* DO TRANSACTION */
        END.   /* DO iFieldCounter */
    END. /* REPEAT */
    DELETE OBJECT hBufferHandle.
    DELETE OBJECT hQueryHandle.
END.  /* FOR EACH _File */.