# Miscellaneous > SQL Scripts >  seperate names

## sleezy

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.

----------


## pan

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

----------


## sleezy

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.

----------


## pan

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 '%,%'

----------

