Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2004
    Location
    Melbourne
    Posts
    144

    Unanswered: removing partial string from column

    how do i update a table which has like two strings in 1 column like

    blog, joe ?

    i want to strip the joe into a new field and the blog into another field

    update Agency
    set firstname= substring(firstname,charindex(' ',firstname)+1 ,len(firstname))

    i managed to strip the first name which is the string at the back but not the last name which is the string at the front
    Last edited by jcwc888; 10-11-05 at 12:20.

  2. #2
    Join Date
    May 2004
    Posts
    125
    Try this to get the lastname:
    lastname = left(column1, patindex('%,%', column1) - 1)

  3. #3
    Join Date
    Apr 2004
    Location
    Melbourne
    Posts
    144
    The error message was :-

    update Agency
    set lastname = left(lastname, patindex('%,%', lastname) - 1)

    Server: Msg 536, Level 16, State 3, Line 1
    Invalid length parameter passed to the substring function.
    The statement has been terminated.

    Dario D'Alessandro, Dario
    Stan Dickson, Stan
    Ray John, Ray
    Juan Sanchez, Juan

    I trying to remove the Dario

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Gotta make sure there is at least one comma, or it errors out. Try this:

    set lastname = left(lastname, charindex(',', lastname + ',') - 1)
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Apr 2004
    Location
    Melbourne
    Posts
    144
    Thanks that did actually work.. not sure how that charindex worked. i was using to REPLACE statements to make it work. It was more efficient using your method.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    If you need a method of parsing name strings into component parts, I have a UDF that will do that too.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •