Results 1 to 11 of 11
  1. #1
    Join Date
    Apr 2004
    Location
    OH
    Posts
    4

    Unanswered: last/first name string

    I need to seperate a last name, first name string. I am migrating data from FoxPro then merging it with data that has been migrated from Paradox. In Paradox the name is kept in: "last, first" format. I need to reformat the name to be: first(space)last. Suggestions....

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Based on the assumption that the first comma in the string marks the end of the last name, you could use
    PHP Code:
    SELECT @comma CharIndex(',', @paradoxName)
    SELECT @last LTrim(RTrim(Left(@paradoxName, @comma 1)))
    SELECT @first LTrim(RTrim(SubString(@paradoxName+ @comma8000))) 
    -PatP

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    select reformatted_name = replace(paradox_name, ', ', ' ') from paradox_table where charindex(', ', paradox_name) > 0

  4. #4
    Join Date
    Apr 2004
    Location
    OH
    Posts
    4
    Originally posted by rdjabarov
    select reformatted_name = replace(paradox_name, ', ', ' ') from paradox_table where charindex(', ', paradox_name) > 0
    This takes care of the comma but doesnt solve the position of the names. Thanks though...

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    select reverse(substring(reverse('last, first'), 1, charindex(', ', 'last, first'))) + ' ' +
    substring('last, first', 1, charindex(', ', 'last, first')-1)

  6. #6
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Just advice - keep last and first names in different fields.

  7. #7
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Originally posted by snail
    Just advice - keep last and first names in different fields.
    I'll drink to that!

  8. #8
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    I was kidding - April 1

  9. #9
    Join Date
    Apr 2004
    Location
    OH
    Posts
    4
    Originally posted by Pat Phelan
    Based on the assumption that the first comma in the string marks the end of the last name, you could use
    PHP Code:
    SELECT @comma CharIndex(',', @paradoxName)
    SELECT @last LTrim(RTrim(Left(@paradoxName, @comma 1)))
    SELECT @first LTrim(RTrim(SubString(@paradoxName+ @comma8000))) 
    -PatP
    Pat,
    this worked like a charm. Thanks a million.

  10. #10
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Pat does strings...

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Originally posted by rdjabarov
    select reverse(substring(reverse('last, first'), 1, charindex(', ', 'last, first'))) + ' ' +
    substring('last, first', 1, charindex(', ', 'last, first')-1)
    You really are evil, but I think you'd already figured that part out!

    -PatP

Posting Permissions

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