Results 1 to 4 of 4

Thread: seperate names

  1. #1
    Join Date
    Sep 2002
    Posts
    218

    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.

  2. #2
    Join Date
    Sep 2002
    Posts
    12
    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

  3. #3
    Join Date
    Sep 2002
    Posts
    218
    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.

  4. #4
    Join Date
    Sep 2002
    Posts
    12
    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
  •