Post reply

Silly Question

  • July 11, 2006 at 8:16 am

    #115763

    I am trying to break a column of data (peoples names) into two new tables first Name and Last name.

    I would like to write something that insert all of the information left of the first space into the  first name table

    then everything right of the first space into the last name table.

    I am having trouble finding the location of the spaces in my query.

     

    Thanks

  • July 11, 2006 at 8:31 am

    #648489

    Before we go into the actual query required to do this, I suggest that you think further about architecture. Surely you don't want separate first- and last-name tables? Separate columns within the same table would be the standard way forward here.

    Anyway, assuming that you come round to agreeing - and have a table called 'names' containing a 'name' field (to be broken down) and 'firstname' and 'surname' fields (to be populated as you describe), something like this should do it:

    update names

    set

    firstname = rtrim(left(name, charindex(' ', name))),

    surname = rtrim(right(name, len(name) - charindex(' ', name))) from names

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • July 11, 2006 at 8:59 am

    #648504

    Phil,

     

    In this case unfortuanatly I am constrained by the application which I am using.  While the architecture you suggest does make much more sense from an insert and IO perspective, unfortunately I do not have that capability. 

    The Charindex is exactly the function I was looking for and could not find.  I should be fine from here.

     

    Thanks a million

  • July 12, 2006 at 3:07 pm

    #648831

    Is the application 'home grown' or is it a package available on the 'open' market ? If it's a package available on the 'open' market then what is its name ?

    I like to have the inside info in order to help my organization to steer clear of poorly designed and or written software.

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

  • July 20, 2006 at 11:11 am

    #650473

    You may want to do some further checking for last names that includes a space like "Mc Donald" or "O Brien".  I believe a case statement can be used in the query for each exception you identify.

    Steve

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply