Name parsing aka splitting names into constituent parts

A bit of a long winded code segment this, it could probably be shortened significantly with a lookup table of titles (like Mr, Mrs, etc.), but this code is written with badly formatted databases in mind. In this case it takes a full name field (like “Mr John D Smith” for example) in table called tblNames and splits it out into different fields for title, first name, middle name and surname. It’s also clever enough to know that “Mr Smith” is a title and surname only and other such permutations, so it’s quite a handy piece of cleanup code.

DECLARE @n varchar(70), @count int, @temp smallint, @temp2 smallint, @temp3 smallint, @temp4 smallint
DECLARE cur CURSOR
FOR SELECT fullname FROM tblNames
For update
SELECT @count = 1

OPEN cur
   FETCH NEXT FROM cur INTO @n

   WHILE (@@fetch_status <> -1) -- and @count < 100) -- the count is for testing on small sets of data first of all.
   BEGIN
      IF (@@fetch_status <> -2)
      BEGIN
         set @n = ltrim(rtrim(@n))
         if left(@n, 3) in ('Dr ', 'Mr ', 'Ms ')
         begin
            update tblNames
            set title = left(@n, 2)
            WHERE current of cur

            set @n = substring(@n, 4, 99)
         end
         if left(@n, 4) in ('Mrs ', 'Rev ', 'Sir ')
         begin
            update tblNames
            set title = left(@n, 3)
            WHERE current of cur
            
            set @n = substring(@n, 5, 99)
         end
         if left(@n, 5) in ('Miss ', 'Prof ')
         begin
            update tblNames
            set title = left(@n, 4)
            WHERE current of cur

            set @n = substring(@n, 6, 99)
         end
         if left(@n, 6) in ('Major ')
         begin
            update tblNames
            set title = left(@n, 5)
            WHERE current of cur
   
            set @n = substring(@n, 7, 99)
         end
         if left(@n, 8 ) in ('Captain ', 'Colonel ')
         begin
            update tblNames
            set title = left(@n, 7)
            WHERE current of cur

            set @n = substring(@n, 9, 99)
         end

         set @n = ltrim(rtrim(@n))
         select @temp = CHARINDEX(' ', @n, 1)

         if (@temp = 0) --no space -treat as surname
         begin
            update tblNames
            set last_name = @n
            WHERE current of cur
         end
         else
         begin
            --get other names
            select @temp2 = 1
            select @temp4 = 0
            --how many spaces....
            WHILE (@temp2 <> 0)
            begin
               select @temp3 = @temp2
               select @temp2 = CHARINDEX(' ', @n, @temp2 +1)
               if @temp4 = 0
               begin
                  set @temp4 = @temp2
               end
            end

            update tblNames
            set last_name = ltrim(rtrim(substring(@n, @temp3+1, 99))),
            middle_name = ltrim(rtrim(substring(@n, @temp4+1, len(@n)-@temp4-(len(@n)-@temp3)))),
            first_name = ltrim(rtrim(substring(@n, 1, @temp4-1)))
            WHERE current of cur

         end
      END
   FETCH NEXT FROM cur INTO @n
   SELECT @count = @count + 1
   END

CLOSE cur
DEALLOCATE cur
GO

Other titles can be added in as appropriate. It doesn’t handle non alphanumerics, so “Mr J. Smith” will give a first name field of “J.” - that may or may not be ok. But code can always be improved upon…