SQL Syntax question

[Dimonet]

I have a question about what syntax to use to perform the following function.

I have a name field that I want to clean up. It's a first name field that sometimes has middle initial. Here is an example of the problem I am facing

John A
Mike
Justin B
W D
Susan
Mr & Mrs
Brian C
Mrs Jones

The field is variable character so there are no spaces at the end of the text. Basically what I want to do is delete those single alpha characters. The only way I can think of doing this is if there are three or more leading characters and then a space and a single alpha character only between A-Z delete that character. But I am not quite sure how to code that and if you look at the example above I wouldn't want to delete the J in Mrs Jones. I only want to delete a single character after it has followed three or more leading characters and stands alone. If anyone could help me with this I would really appreciate it.

 

[tlbroadbent]

This query update the name only if the 2nd character from the end is a space.

Update Table Set Name=Left(Name,len(Name)-2)
Where substring(Name,len(Name)-1,1)=' ' Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[Dimonet]

Thanks I will give it a try..

 

[Dimonet]

Hey it worked! Thanks so much for your help!! If you don't mind and you have time could you explain what the 'len' command is doing? I pretty much understand the rest of the code.

Thanks,
Dimo

 

[tlbroadbent]

Teh LEN function returns the length of the data in the column or string. Trailing spaces are not counted. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.