Results 1 to 15 of 15

Thread: Name Split

  1. #1
    Join Date
    Jun 2004
    Posts
    57

    Unanswered: Name Split

    Quick question.

    I've got a CHAR (70) field called NAME that has a first and last name separated by a space. I want to split it into two fields FIRST and LAST -- with all the characters to the left of the space a first name and all the characters to the right of the space as last name. I couldn't find a string function that would let me do this simply (it may be right in front of me and I missed it).

    Thanks in advance.

    Ray

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    you have to use a couple and think dynamically. I think you want something like so:

    SELECT RIGHT(thename,LEN(thename) - CHARINDEX(' ',thename)) AS lastname,
    LEFT(thename,CHARINDEX(' ',thename)) AS firsttname
    FROM yourTable
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i think you'll find that there will be many with 3 names, and that it's better to split on the last space, not the first

    (although nothing is perfect, e.g. John Smith III will have to be corrected)

    here's some code i just happened to have lying around --
    Code:
    select left(fullname,len(rtrim(fullname))
                        - charindex(' ',reverse(rtrim(fullname))))
                  as firstname
         , ltrim(right(rtrim(fullname)
                        , charindex(' ',reverse(rtrim(fullname))))) 
                  as lastname
      from yourtable
    also, note that the code, whichever you use, should also work on single names like Cher

    Last edited by r937; 02-17-05 at 01:16.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Oh, that's a low blow, Canadiene!

    Let's make a deal. YOU don't mention Cher, and WE won't mention Celine Dion.

    Detente', OK?
    If it's not practically useful, then it's practically useless.

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

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    It's ya birfday, rkobs.

    Use the attached function with this syntax:

    select dbo.FormatName([YourField], 'F') as FirstName
    select dbo.FormatName([YourField], 'L') as FirstName
    Attached Files Attached Files
    If it's not practically useful, then it's practically useless.

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

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    nothin wrong with Cher -- had the hots for her in the 60s and still do

    along with Celine Dion, i would also appreciate it if you wouldn't mention Mutt Lange's wife Shania Twain, Avril Lavigne, Anne Murray, Sarah McLachlan, Nellie Furtado, and Alanis Morissette -- they all make my ears hurt
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    wow, i have a code snippet lying around, and you have an entire application!!

    nice job

    just curious, when did you write that?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Celine Dion, Shania Twain, Avril Lavigne, Anne Murray, Sarah McLachlan, Nellie Furtado, and Alanis Morissette?

    Are you some sort of misogynist?
    If it's not practically useful, then it's practically useless.

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

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I've written and revised it over the last seven or eight years.

    It was originally an Access VB function.
    If it's not practically useful, then it's practically useless.

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

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by blindman
    Are you some sort of misogynist?
    not at all, i love women, i've married several of them and would do so again if i weren't so old (women don't usually find old guys all that attractive)

    no, it's just that i don't like those particular singers

    i mean, you started it with the female singers

    you can have Gordon Lightfoot and the Bare Naked Ladies too, if it makes you happy

    i'll keep Rush, Pat Travers, Triumph, Kim Mitchell, Saga, Robin Trower, Jeff Healey ... all from Toronto (but not all still together)
    Last edited by r937; 02-17-05 at 11:36.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    And by the way, Alanis Morissette is an American (as of today...).

    http://apnews.excite.com/article/200...D88A92MG0.html
    If it's not practically useful, then it's practically useless.

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

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i am flummoxed
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by r937
    not at all, i love women, i've married several of them and would do so again if i weren't so old (women don't usually find old guys all that attractive)
    I'm not buying that one... I thought you were married now. My guess is that you wouldn't need to work hard to be married, if you decided that you wanted to be married. Age seems to have very little to do with it, although once you get past a certain point you do have less interest to the twenty-somethings! I guess I don't consider that a bad thing, it would be tough to try to raise a wife when I'm accustomed to thinking of them as partners.

    Quote Originally Posted by r937
    i am flummoxed
    That doesn't happen often. It must be some function, I'll have to look at it in more detail!

    -PatP

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Pat Phelan
    I thought you were married now.
    nope, happily divorced

    the kids live with me every two weeks

    and i don't think i will ever stop being attracted to the hot twenty-somethings

    at least i have the good sense not to try to do anything about it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by r937
    and i don't think i will ever stop being attracted to the hot twenty-somethings

    at least i have the good sense not to try to do anything about it
    Like Lamborghini's, they certainly catch your eye, and I can see how they could be marvelous fun for an afternoon under the right conditions, but would you really want one? Even if someone offered to give me a Countach or a Murciélago, I can't imagine what I'd do with one. I don't often handle anything like them, and although it would be fun it would most likely get me killed!

    Along the same lines, I can't imagine dating someone in their twenties. I can only think of a very small number of women in their thirties that might interest me. I'm entirely content with the lady that I'm seeing, and she's a bit older than I am.

    Although I have to admit that it was hilarious when I wandered over to the pool at a DR site last year and said "Greetings, Gorgeous" to an eighteen year old who promptly offered me a drink. Several of the guys I work with were picking up their jaws until she said "Mom's over on the other side of the pool, can you spread some sun tan lotion on me?" She was my girlfriend's daughter, but none of the folks I work with would recognize her. Simple pleasures!

    -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
  •