-
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.
Last edited by sleezy; 11-20-2002 at 03:17 PM.
-
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 '%,%'
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|