Post reply

Extracting a portion of string from a field

  • October 8, 2008 at 3:41 am

    #75335

    Hi,

    This is probably a simple one for you guys.

    We have a table which contains the full names of people and I need to extract the surname

    (I know had the database been created correctly there would have been a surname field

    but you can't have everything!:hehe:)

    some of the table data is shown below

    name

    Lisa Veronica Plant and Anthony Derek Plant

    Thomas Joseph Mylod and Janet Mylod

    Leonard John Moore and Christine Ann Moore

    Stephen Robert Fagan and Judith Mary Fagan

    I need to find the position of the last space in the text then I can use the right string function to extract the surname.

    Thanks

  • October 8, 2008 at 5:31 am

    #882187

    Take a look at this, courtesy of Jeff Moden:

    http://www.sqlservercentral.com/articles/TSQL/62867/

    Seth Phelabaum

    Consistency is only a virtue if you're not a screwup. 😉

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

  • October 8, 2008 at 5:45 am

    #882193

    this code will get you the lastname as long as it the after the last space in the string.

    declare @Name varchar(100)

    set @name = 'Stephen Robert Fagan and Judith Mary Fagan'

    select reverse(left(reverse(@name),charindex(' ',reverse(@name))))

  • October 8, 2008 at 5:48 am

    #882195

    Thanks Guys

    much appreciated.

  • October 8, 2008 at 5:48 am

    #882197

    Nice, a new function(REVERSE) to play with. Thanks Steve.

    Seth Phelabaum

    Consistency is only a virtue if you're not a screwup. 😉

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

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

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