Name Parse...how to eliminate middle initial?

[starbase59]

How do I eliminate the middle initial and nickname (in parens) in these text string examples?
My ultimate goal is to parse a simple first and last name, with no middle initial or other garbage in the string.

THOMAS C. (SKIP) HANKS
VERN D. (DICK) HOLLYOAK

Thanks in advance.

 

[r937]

SELECT [blue]LEFT(fullname,CHARINDEX(' ',fullname)-1) AS firstname[/blue]
     , [blue]REVERSE(LEFT(REVERSE(fullname),CHARINDEX(' ',REVERSE(fullname))-1)) AS lastname[/blue]
  FROM ( SELECT 'Thomas C. (Skip) Hanks' AS fullname
         UNION ALL
         SELECT 'Vern D. (Dick) Hollyoak'
         UNION ALL
         SELECT 'Todd Kamalfezchuk' ) AS d

will not work unless the fullname contains at least one space

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[ousoonerjoe]

That will also cause a problem for 2-part first names such as "Mary Ann" or initials for first names like "T J".

Be sure to identify these items so that they can be manually cleaned up after wards.

--------------------------------------------------
Bluto: What? Over? Did you say "over"? Nothing is over until we decide it is! Was it over when the Germans bombed Pearl Harbor? No!
Otter: Germans?
Boon: Forget it, he's rolling.
--------------------------------------------------