vsllc
asked on
Remove blank space at end of text field.
I have a text field in a table with a blank space at the end of every record. The data before the blank varies in length. I need to join two tables using thus field. How can I remove this blank space?
The file is like this for what looks like all the text fields (some are numeric and date and look OK). Can I remove thus space in all the fields at once?
Thanks.
The file is like this for what looks like all the text fields (some are numeric and date and look OK). Can I remove thus space in all the fields at once?
Thanks.
ASKER
Data dump. I used the trim function in an update query and it removed the trailing blank, but I found a few with a leading space that was not deleted. From what I've found, trim should remove both leading and trailing. Ideas?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I can't upload db. After running, the space still exists. His can I determine what the character is?
select all the records that have this space? and paste to a newtable
create a new db, import the newtable
upload the new db
ASKER
Got it! I ran the ASC function. The char is a 160. Thanks for the help.
now just run an update query like what i posted above
Update table
set [field]=replace([field],ch
Update table
set [field]=replace([field],ch
Backup before testing this update query. (Obviously amend for your own table/field names)
Update tablename set fieldname = trim(fieldname)