Need help with sql code

[tchavez]

SELECT    VoterName,
          SUBSTRING(VoterName, 1, CHARINDEX(',', VoterName) - 1) AS LName,
          LTRIM(SUBSTRING(VoterName, CHARINDEX(',', VoterName) + 1, CHARINDEX(' ', LTRIM(SUBSTRING(VoterName, CHARINDEX(',', VoterName) + 1, LEN(VoterName)))))) AS FName,
          SUBSTRING(LTRIM(SUBSTRING(VoterName, CHARINDEX(',', VoterName) + 1, LEN(VoterName))), CHARINDEX(' ', LTRIM(SUBSTRING(VoterName, CHARINDEX(',', VoterName) + 1, LEN(VoterName)))) + 1, LEN(LTRIM(SUBSTRING(VoterName, CHARINDEX(',', VoterName) + 1, LEN(VoterName))))) AS MName
FROM      [VoterFileMarch]
WHERE     (CHARINDEX(',', VoterName) > 0)

The problem I am having is that if there is no middle name then it puts the first and middle in the MName field and nothing in the FName field.

 

[jmcilhinney]

Is there any possibility of changing the database schema to store the names in seperate columns, as they should be, because that is diabolical code to have to write.

 

[tchavez]

Wish it was that easy

I wish it was that easy I get the database from the State of Hawaii has 650,000 records all of them are Jones, Jim M. style or Jones, Jamie style.

I'm trying to get it extracted out into a better format so I can insert it into a better table scheme