Update query to remove leading and trailing spaces from all columns throughout table

[kb44]

Hi all,

I have the need to trim leading and trailing spaces from multiple columns populated by individual's names. Each field is unique.

From what I understand, Trim() will only work with an individual string (a unique name like "John Hancock", but will not work with "Bob Hancock" unless you specify that name.

Please tell I am wrong and that I can use Trim() with the whole table! I am looking to create an update query.

I am a newbie to Access so thank you for any and all help.

 

[plog]

(a unique name like "John Hancock", but will not work with "Bob Hancock"

Huh? What makes Bob unique and John not? I don't follow what idea you were trying to convey there.

Trim() removes leading and trailing spaces on a string/field:

Trim("  This String      ")  returns "This String"

Trim("This String  ") returns "This String"

Trim("This String")  returns "This String"

Trim ("This         String    ") returns "This         String"

~~~~

[FieldName] = "  John Hancock   "
Trim([FieldName]) returns "John Hancock"

~~~~

tblYourTable
FieldName
" John Hancock "
"Bob Hancock "
"    Plog      Hancock    "

UPDATE tblYourTable SET FieldName=Trim(FieldName);

tblYourTable
"John Hancock"
"Bob Hancock"
"Plog      Hancock"

 

[kb44]

Lol. All I was trying to say is that they are unique and that trimming spaces from 800 plus unique names (sorry, I should have mentioned that there were so many) by typing each name in a query would be a real pain.

 

[jdraw]

Are these "names" recorded in your table in separate fields as FirstName and LastName?

If so, you write some code to read each of the names and update those names with Trim(yourFieldName). You certainly don't have to be
"typing each name in a query "

Depending on the fields involved, you might be able adapt

UPDATE YourTable
SET text_field1 = Trim(text_field1), text_field2 = Trim(text_field2);

 

[kb44]

They are together in one field with a space in between the first and last name.

 

[jdraw]

You might want to separate the names into unique fields, you can always join them together with a query. I would suggest you consider separate fields --it is the common advice for table design. Use "atomic fields" --one fact, one field.

 

[kb44]

You might want to separate the names into unique fields, you can always join them together with a query. I would suggest you consider separate fields --it is the common advice for table design. Use "atomic fields" --one fact, one field.

Thanks. I agree that one field per name is how this should have been done from the start and that it should be changed.

I was looking for a way to do the whole list in one shot in an update query but I guess that is not an option.

To separate them and then join them back together is a great idea. The people on the list are from different countries and there are a ton of unique first and last names with 3 or 4 words for each person. Im sure that will complicate things.

 

[Frothingslosh]

You absolutely can do the whole list in one shot, as plog pointed out. There's absolutely no need to type out every individual name.

UPDATE TableName SET FieldName = Trim([FieldName]), FieldName2 = Trim([FieldName2]);

Just be DAMNED sure you use the same field name on both sides of each equals sign, or you hose your data for good. Backing up your data first would not be amiss.

If you need to trim interior spaces, that's doable as well but is a bit more complicated.

 

[jdraw]

Name parsing can be tedious -especially compound names-- and often requires looking for patterns and addressing such patterns. It certainly requires analysis and testing before jumping into action against an operational database.

Do you have some examples of the "difficult ones"?

 

[kb44]

Huh? What makes Bob unique and John not? I don't follow what idea you were trying to convey there.

Trim() removes leading and trailing spaces on a string/field:

Trim("  This String      ")  returns "This String"

Trim("This String  ") returns "This String"

Trim("This String")  returns "This String"

Trim ("This         String    ") returns "This         String"

~~~~

[FieldName] = "  John Hancock   "
Trim([FieldName]) returns "John Hancock"

~~~~

tblYourTable
FieldName
" John Hancock "
"Bob Hancock "
"    Plog      Hancock    "

UPDATE tblYourTable SET FieldName=Trim(FieldName);

tblYourTable
"John Hancock"
"Bob Hancock"
"Plog      Hancock"

I apologize. You told me how to solve this and I totally missed it!

 

[kb44]

You absolutely can do the whole list in one shot, as plog pointed out. There's absolutely no need to type out every individual name.

UPDATE TableName SET FieldName = Trim([FieldName]), FieldName2 = Trim([FieldName2]);

Just be DAMNED sure you use the same field name on both sides of each equals sign, or you hose your data for good. Backing up your data first would not be amiss.

If you need to trim interior spaces, that's doable as well but is a bit more complicated.

I completely missed that earlier. Thanks for pointing it out. Also ty for the warning about matching the names!

 

[kb44]

Name parsing can be tedious -especially compound names-- and often requires looking for patterns and addressing such patterns. It certainly requires analysis and testing before jumping into action against an operational database.

Do you have some examples of the "difficult ones"?

The names go as far as first, middle, last, lastlast-hyphenated names. It's crazy. They even have some nick names in parenthesis to the right of the full name. I am guessing that is what the person wants to be called.

 

[Frothingslosh]

The names go as far as first, middle, last, lastlast-hyphenated names. It's crazy. They even have some nick names in parenthesis to the right of the full name. I am guessing that is what the person wants to be called.

"Karl-Franz Helmut Markus Heinrich Schmidt-Miller III, Esq. (Bob)"

Yep, that is PRECISELY why we always recommend storing each name in its own field.

 

[Gasman]

So would we have a table that holds 4 forename fields and a surname field, when most people would not have that many forenames and have lots of empty fields?, or have a table for forenames, or even all names with an indicator for their order?

1	Karl-Franz
2	Helmut
3	Markus
4	Heinrich
5	Schmidt-Miller III Esq
6	Bob

"Karl-Franz Helmut Markus Heinrich Schmidt-Miller III, Esq. (Bob)"

Yep, that is PRECISELY why we always recommend storing each name in its own field.

 

[jdraw]

As the database designer, you have to identify and confirm the requirements/specification.
Your life will be much easier if you get to one format for names and initials. It is often not a 1 step process. You may have to make several iterations to correct the data you have to get it into the "new standard format" AND you should ensure that you get all new data into your "new standard format" as it is input. You don't want to have to review and re-parse every time you use the database.

I am attaching a Name parsing function and a test routine with results of the test. It may be helpful for ideas, but you'll have to determine and confirm what format is acceptable by management.

Good luck.

 

[Frothingslosh]

So would we have a table that holds 4 forename fields and a surname field, when most people would not have that many forenames and have lots of empty fields?, or have a table for forenames, or even all names with an indicator for their order?

1	Karl-Franz
2	Helmut
3	Markus
4	Heinrich
5	Schmidt-Miller III Esq
6	Bob

Honestly? If I needed to track all of that, I'd break it down like this:

First Name: Karl-Franz
Middle Name: Helmut Markus Heinrich
Last Name: Schmidt-Miller
Suffix: III
Honorific: Esq
Nickname: Bob

If I only had the three name fields, I'd toss the honorific and either toss the suffix or put it after whichever name tends to get printed last (I've seen it done on either name based on whether the format is Last, First or First Last). I've rarely seen nicknames get saved, and they've always been in their own field. If it HAD to be saved in a regular name field, I think it would likely be formatted as Name (Nickname).

A field for maiden name is also pretty common.

While my company doesn't store suffix, honorific, and nickname, I've seen a LOT of online forms that have spots available for at least the first two, so it's not THAT uncommon to have the fields added just in case.

 

[kb44]

"Karl-Franz Helmut Markus Heinrich Schmidt-Miller III, Esq. (Bob)"

Yep, that is PRECISELY why we always recommend storing each name in its own field.

That sounds about right!

 

[kb44]

As the database designer, you have to identify and confirm the requirements/specification.
Your life will be much easier if you get to one format for names and initials. It is often not a 1 step process. You may have to make several iterations to correct the data you have to get it into the "new standard format" AND you should ensure that you get all new data into your "new standard format" as it is input. You don't want to have to review and re-parse every time you use the database.

I am attaching a Name parsing function and a test routine with results of the test. It may be helpful for ideas, but you'll have to determine and confirm what format is acceptable by management.

Good luck.

That's some hard core name parsing right there! Thanks.

I wish I was the designer! This comes from HR. Hard to believe that a large corporation would allow this.

 

[Gasman]

Well my full name is A B C, however I go by the name of B. I have never used A. It was selected supposedly to honour a Battle of Britain pilot who was killed, so the first born male would be called A.

Nightmare when I go to the doctor's surgery as I have to make a special effort to hear that name when they call my turn, which I never use.

How spies do it, I do not know.

 

[kb44]

Honestly? If I needed to track all of that, I'd break it down like this:

First Name: Karl-Franz
Middle Name: Helmut Markus Heinrich
Last Name: Schmidt-Miller
Suffix: III
Honorific: Esq
Nickname: Bob

If I only had the three name fields, I'd toss the honorific and either toss the suffix or put it after whichever name tends to get printed last (I've seen it done on either name based on whether the format is Last, First or First Last). I've rarely seen nicknames get saved, and they've always been in their own field. If it HAD to be saved in a regular name field, I think it would likely be formatted as Name (Nickname).

A field for maiden name is also pretty common.

While my company doesn't store suffix, honorific, and nickname, I've seen a LOT of online forms that have spots available for at least the first two, so it's not THAT uncommon to have the fields added just in case.

Thanks. Ill keep this format in mind.. in case I get the opportunity to fix it. You never know!