Post reply

Help with String manipulation

  • July 14, 2005 at 12:24 am

    #67736

    I have the following string as a staffname Column in a table.

    Joe Bloggs

    Peter Smith

    Sue Smith

    I need a script to be able to seperate the firstname from the surname becuase I need it to go into a new table that has seperate columns for the firstname & surname.

    I did start using the padstring but that only gives me an integer as to when the occurrence of a space is seperating the firstname & surname.

    Any one know how to do this?

    Kindest Regards,

  • July 14, 2005 at 12:59 am

    #574266

    Try something like

    select substring(staffname, 1, charindex(' ', staffname))

    This requires exactly one firstname and one surname - as in your sample data. If this is not enough, please describe what the expected outcome is if there is 0, 1, 3 or more words in the staffname column.

     

  • July 14, 2005 at 1:12 am

    #574269

    Thanks. I'll give it a go.

    Kindest Regards,

  • July 14, 2005 at 10:23 pm

    #574661

    Try this one:

    DECLARE @Name varchar(80)

    SET @Name = 'Sara Jane Smith-Jones'

    SELECT

      RTRIM(LEFT(@Name,LEN(@Name)-CHARINDEX(' ',REVERSE(@Name)))) AS FirstName

      , LTRIM(RIGHT(@Name,CHARINDEX(' ',REVERSE(@Name)))) AS LastName

    Andy

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

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