declare @CustId varchar(100)
declare @RowNum int
declare CustList cursor for
select top 10 defendant_1 from CaseInformation
	OPEN CustList
	FETCH NEXT FROM CustList 
	INTO @CustId
	set @RowNum = 0 
	WHILE @@FETCH_STATUS = 0
	BEGIN
	  set @RowNum = @RowNum + 1
	  --set @CustId = (SELECT idx, value FROM dbo.fn_Split(@CustId, 'space')
	 
		print @CustId
	  FETCH NEXT FROM CustList 
	    INTO @CustId
	END
	CLOSE CustList
	DEALLOCATE CustList

UPDATE caseinformation
SET Firstname = SUBSTRING (defendant_1, 1, (CHARINDEX(' ', defendant_1) - 0)) 

UPDATE caseinformation
SET Lastname = LTRIM(right(Defendant_1,charindex(' ',reverse(Defendant_1))))

declare @table table (table_id int identity(1,1), defendant varchar(100))
insert into @table (defendant)
	select 'Nathan L Skerl' union
	select 'Adam J Skerl' union
	select 'Natalie R Skerl' union
	select 'John Skerl'


-- initial state
select * from @table

-- replace space with period
update @table
set defendant = case 
			when LEN(LTRIM(RTRIM(defendant))) - LEN(REPLACE(LTRIM(RTRIM(defendant)), ' ', '')) = 2 
				then replace(defendant, ' ', '.')
			when LEN(LTRIM(RTRIM(defendant))) - LEN(REPLACE(LTRIM(RTRIM(defendant)), ' ', '')) = 1
				then replace(defendant, ' ', '..')
			else defendant
		end


select 	parsename(defendant,3) as first_name,
	case 
		when len(parsename(defendant,2)) = 1 then parsename(defendant,2)
		else null 
	end as middle_initial,
	parsename(defendant,1) as last_name
from @table

quote:

---

Props to Rob and Graz: http://www.sqlteam.com/item.asp?ItemID=15044 height="1" noshade id="quote">