seperate names

l would like to separate the surname from the initial and the residentialaddress.
The records looks like this

NAME
----------
stanley MS
Johnson NP
Roberts NE
VE Jones
VD Maiden
Miller I
KOBANE M


RESIDENTIAL
--------------
matatiele,4730
mamakato,3310
kokstad,4700
Flagstaff,4810
riverside,3239
Silverton,4360
Donesdale,1600


The problem is that sometimes the surname has the initials in the same column.The order can be initials then surn or surn then initials.

My second problem is to separate the residential into area and postalCode.

The second problem can be resolved using substring and padindex e.g.:

select substring(RESIDENTIAL,1,patindex('%,%',RESIDENTIAL )-1) AS Area, substring(RESIDENTIAL,patindex('%,%',RESIDENTIAL)+ 1,4)AS PostalCode

This is what l'm running

Select
Substring(RESIDENTIAL,1,Patindex('%,%',RESIDENTIAL )-1) AS Area
,Substring(RESIDENTIAL,Patindex('%,%',RESIDENTIAL) +1,4)AS PostalCode
From nadd2



and l get the following error

(514 row(s) affected)

Server: Msg 536, Level 16, State 3, Line 1
Invalid length parameter passed to the substring function.

The reason for the error is that maybe there values with no comma "," in it. To exclude these values use the where clause. Run this script to see if the error occurs:

Select
Substring(RESIDENTIAL,1,Patindex('%,%',RESIDENTIAL
)-1) AS Area
,Substring(RESIDENTIAL,Patindex('%,%',RESIDENTIAL
)+1,4)AS PostalCode
From nadd2
WHERE Residential like '%,%'