Consultor Eletrônico

Leading/trailing spaces:When they exist & How to Remove

Leading/Trailing spaces When they exist in my db & How to Remove?

There appear to be two times when you may encounter leading and/or
trailing spaces in your database.

1. There are times when a user may wish to have leading or trailing
spaces in their database. The way a user may do this is by using an
ASSIGN statement.

For example: customer.name = "John Doe "

In a case like this, Progress will not automatically trim off the
trailing spaces in your database when you have explicitly requested
the additional spaces via the ASSIGN statement. There are other 4GL
statements (such as UPDATE and SET) that when used will cause Progress
to automatically truncate any additional leading or trailing spaces.

2) The second way that you may find leading and/or trailing spaces in
your database is if you load the data into your database when the
spaces are already pre-existing in the data. Therefore, if you are
using the data admin tool to load data into your database and it
already has data with leading and/or trailing spaces Progress will not
truncate those spaces.

**********************************************************************

In some cases users are unaware that they have inadvertently added the
additional leading/trailing spaces to their data. If a user has
encountered this for whatever reason they may be curious as to how
they may be able to remove those unwanted spaces. A possible solution
to help remove leading and trailing spaces from your database is as
follows:
(This is completely UNSUPPORTED but may be used to help eliminate the
leading and trailing spaces in your database)

Procedure to remove leading/trailing spaces from your existing
database:

1) Customer runs clean-mst.p

def var x as char format "x(50)".
def var y as char format "x(50)".
output to clean.p.

put "output to clean.out." skip.
for each _file where _file-num gt 0:

x = "for each " + _file._file-name + ":".
put "put '" x "'." skip.
put "put skip." skip.
put x skip " assign ".
for each _field where (_field._file-recid = recid(_file)) and
(_field._data-type = "character"):
y = " " + _field._field-name +
" = trim (" + _field._field-name + ")".
put y skip.
end.
display "." skip "end." skip skip.
end.
display "quit.".


*** The clean-mst.p will generate a new file from your metaschema
called clean.p

2) Once clean.p has been created then proceed to execute it.

Example of clean.p (This is JUST an example of what your clean.p
may look like. ùDo not use this program as your clean.p program as the
clean-mst.p program will generate your OWN clean.p from your
metaschema)

output to clean.out.
put 'for each customer: '.
put skip.
for each customer:
assign Address = trim (Address)
Address2 = trim (Address2)
City = trim (City)
Contact = trim (Contact)
Name = trim (Name)
Phone = trim (Phone)
Sales-region = trim (Sales-region)
Sales-rep = trim (Sales-rep)
St = trim (St)
Tax-no = trim (Tax-no)
Terms = trim (Terms)

.
end.

quit.

3) All output that is created from clean.p will be written to a file
called clean.out.

At this point all the leading and trailing spaces should have been
removed from your database. If you need to only delete certain
leading/trailing spaces you will need to modify the code accordingly
as this is only meant as one possible solution.


CSG 2/10/98

Progress Software Technical Support Note # 17680