If I haven't already, I'm about to bore your socks off.

CAUTION:  Work post.  Don't worry.  I won't regale you with stories about the moldy food in the break room (although I found some on Monday), nor will I whine about my coworkers (mostly because so far, they're all pretty cool and WAAAY more qualified than I).  What I'm about to do is much worse than that.  I'm going to tell you about what I did today.  For seven hours.  Hopefully, it won't take you seven hours to read through this.  If so, I apologize (and suggest that you get a tutor).

What I was trying to do is turn "Lastname,Firstname Middlename" into "Lastname"  "Firstname"  "Middlename."  I know.  It's not very exciting at all.  But I was trying to do it anyway, and my google searches were yielding little by way of instruction.  What I did find was this site which gave me this code:

SELECT
  FIRST_NAME.ORIGINAL_INPUT_DATA
 ,FIRST_NAME.TITLE
 ,FIRST_NAME.FIRST_NAME
 ,CASE WHEN 0 = CHARINDEX(' ',FIRST_NAME.REST_OF_NAME)
       THEN NULL  --no more spaces?  assume rest is the last name
       ELSE SUBSTRING(
                       FIRST_NAME.REST_OF_NAME
                      ,1
                      ,CHARINDEX(' ',FIRST_NAME.REST_OF_NAME)-1
                     )
       END AS MIDDLE_NAME
 ,SUBSTRING(
             FIRST_NAME.REST_OF_NAME
            ,1 + CHARINDEX(' ',FIRST_NAME.REST_OF_NAME)
            ,LEN(FIRST_NAME.REST_OF_NAME)
           ) AS LAST_NAME FROM
  (
  SELECT
    TITLE.TITLE
   ,CASE WHEN 0 = CHARINDEX(' ',TITLE.REST_OF_NAME)
         THEN TITLE.REST_OF_NAME --No space? return the whole thing
         ELSE SUBSTRING(
                         TITLE.REST_OF_NAME
                        ,1
                        ,CHARINDEX(' ',TITLE.REST_OF_NAME)-1
                       )
    END AS FIRST_NAME
   ,CASE WHEN 0 = CHARINDEX(' ',TITLE.REST_OF_NAME)  
         THEN NULL  --no spaces @ all?  then 1st name is all we have
         ELSE SUBSTRING(
                         TITLE.REST_OF_NAME
                        ,CHARINDEX(' ',TITLE.REST_OF_NAME)+1
                        ,LEN(TITLE.REST_OF_NAME)
                       )
    END AS REST_OF_NAME
   ,TITLE.ORIGINAL_INPUT_DATA
  FROM
    (  
    SELECT
      --if the first three characters are in this list,
      --then pull it as a "title".  otherwise return NULL for title.
      CASE WHEN SUBSTRING(TEST_DATA.FULL_NAME,1,3) IN ('MR ','MS ','DR ','MRS')
           THEN LTRIM(RTRIM(SUBSTRING(TEST_DATA.FULL_NAME,1,3)))
           ELSE NULL
           END AS TITLE
      --if you change the list, don't forget to change it here, too.
      --so much for the DRY prinicple...
     ,CASE WHEN SUBSTRING(TEST_DATA.FULL_NAME,1,3) IN ('MR ','MS ','DR ','MRS')
           THEN LTRIM(RTRIM(SUBSTRING(TEST_DATA.FULL_NAME,4,LEN(TEST_DATA.FULL_NAME))))
           ELSE LTRIM(RTRIM(TEST_DATA.FULL_NAME))
           END AS REST_OF_NAME
     ,TEST_DATA.ORIGINAL_INPUT_DATA
    FROM
      (
      SELECT
        --trim leading & trailing spaces before trying to process
        --disallow extra spaces *within* the name
        REPLACE(REPLACE(LTRIM(RTRIM(FULL_NAME)),'  ',' '),'  ',' ') AS FULL_NAME
       ,FULL_NAME AS ORIGINAL_INPUT_DATA
      FROM
        (
        --if you use this, then replace the following
        --block with your actual table
              SELECT 'GEORGE W BUSH' AS FULL_NAME
        UNION SELECT 'SUSAN B ANTHONY' AS FULL_NAME
        UNION SELECT 'ALEXANDER HAMILTON' AS FULL_NAME
        UNION SELECT 'OSAMA BIN LADEN JR' AS FULL_NAME
        UNION SELECT 'MARTIN J VAN BUREN SENIOR III' AS FULL_NAME
        UNION SELECT 'TOMMY' AS FULL_NAME
        UNION SELECT 'BILLY' AS FULL_NAME
        UNION SELECT NULL AS FULL_NAME
        UNION SELECT ' ' AS FULL_NAME
        UNION SELECT '    JOHN  JACOB     SMITH' AS FULL_NAME
        UNION SELECT ' DR  SANJAY       GUPTA' AS FULL_NAME
        UNION SELECT 'DR JOHN S HOPKINS' AS FULL_NAME
        UNION SELECT ' MRS  SUSAN ADAMS' AS FULL_NAME
        UNION SELECT ' MS AUGUSTA  ADA   KING ' AS FULL_NAME    
        ) RAW_DATA
      ) TEST_DATA
    ) TITLE
  ) FIRST_NAME 

The problem with that code is that it puts everything into fakey jake areas and calls on them later, blah blah blah long story short, I'm too stupid to figure out how to take that code and put it into my own select statement and get other fields that I want.  So....it only took me seven hours, but I did it.  Here it is.  So pretty.  So nice...

/*WHEN NAMES ARE FORMATTED: "LAST,FIRST MIDDLE" (NO SPACE BEFORE OR AFTER THE COMMA)
OR AS COMPANY NAMES, IE: "21ST CENTURY INSURANCE" THIS CODE WILL RETURN 3 COLUMNS
"FIRST_NAME" "MIDDLE_NAME" "LAST_NAME" WITH FIELDS POPULATED CORRECTLY. IN THE INSTANCE
WHERE THE NAME IS A COMPANY NAME (AS LONG AS THERE ARE NO COMMAS IN THE NAME) IT WILL RETURN
THE COMPANY NAME IN THE "LAST_NAME" FIELD.
THIS IS WAY CONVOLUTED, SO IF THERE'S A BETTER WAY, PLEASE LET ME KNOW. BUT WITH THIS
TECHNIQUE, YOU CAN SLIDE IT INTO YOUR NORMAL SELECT STATEMENT W/O HAVING TO CREATE TEMP TABLES, ETC.
OBVIOUSLY, YOU'LL HAVE TO CHANGE YOUR TABLE AND COLUMN NAMES TO FIT WHAT TABLES YOU'RE USING*/SELECT
YOUR_FIELD
,CASE WHEN 0 = CHARINDEX(',',YOURTABLE.YOUR_FIELD)
THEN NULL --no comma? it's the last name WHEN 0 >= (LEN(substring(YOURTABLE.YOUR_FIELD,CHARINDEX(' ',YOURTABLE.YOUR_FIELD,+1),LEN(YOURTABLE.YOUR_FIELD))) -
LEN(SUBSTRING(YOURTABLE.YOUR_FIELD,CHARINDEX(',',YOURTABLE.YOUR_FIELD,+1),LEN(YOURTABLE.YOUR_FIELD))))*-1
THEN SUBSTRING(YOURTABLE.YOUR_FIELD,
(CHARINDEX(',',YOURTABLE.YOUR_FIELD)+1),LEN(YOURTABLE.YOUR_FIELD))
--need to pull in only the first name, not the first name w/ the middle initial at the end
--if there's no space after the first name, sql gets confused as to what is the first name ELSE SUBSTRING(YOURTABLE.YOUR_FIELD
,CHARINDEX (',',YOURTABLE.YOUR_FIELD,+1)+1
,(LEN(substring(YOURTABLE.YOUR_FIELD,CHARINDEX(' ',YOURTABLE.YOUR_FIELD,+1),LEN(YOURTABLE.YOUR_FIELD))) -
LEN(SUBSTRING(YOURTABLE.YOUR_FIELD,CHARINDEX(',',YOURTABLE.YOUR_FIELD,+1),LEN(YOURTABLE.YOUR_FIELD))))*-1
)
END AS FIRST_NAME
,CASE WHEN 0 = CHARINDEX(',',YOURTABLE.YOUR_FIELD)
THEN NULL --no comma? it's the last name WHEN 0 = CHARINDEX(' ',YOURTABLE.YOUR_FIELD)THEN NULL --no spaces? assume it's a last nameWHEN 1 <= CHARINDEX(' ',SUBSTRING( YOURTABLE.YOUR_FIELD
,1
,CHARINDEX(',',YOURTABLE.YOUR_FIELD)-1
))THEN NULL -- there is a space before the comma? assume the space is in the last nameELSE
SUBSTRING(YOURTABLE.YOUR_FIELD
,1 + CHARINDEX(' ',YOURTABLE.YOUR_FIELD)
,LEN(YOURTABLE.YOUR_FIELD)
)
END AS MIDDLE_NAME
, CASE WHEN 0 = CHARINDEX(',',YOURTABLE.YOUR_FIELD)THEN YOURTABLE.YOUR_FIELD --if there's no comma, it's a last nameELSE SUBSTRING (YOURTABLE.YOUR_FIELD
,1
,CHARINDEX(',',YOURTABLE.YOUR_FIELD)-1
)END AS LAST_NAME

FROM YOURTABLE

Actually, it's a whole lot of crazy, and I'm sure there's a better way to do this.  But in case you're searching for it, here's an easy way to grab portions of text from a field in SQL.  Hooray for determination!