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.

Rayyou 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|||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 --
select left(fullname,len(rtrim(fullname))
- charindex(' ',reverse(rtrim(fullname))))
as firstname
, ltrim(right(rtrim(fullname)
, charindex(' ',reverse(rtrim(fullname)))))
as lastname
from yourtablealso, note that the code, whichever you use, should also work on single names like Cher

:)|||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?|||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|||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|||wow, i have a code snippet lying around, and you have an entire application!!

nice job

just curious, when did you write that?|||Celine Dion, Shania Twain, Avril Lavigne, Anne Murray, Sarah McLachlan, Nellie Furtado, and Alanis Morissette?

Are you some sort of misogynist?|||I've written and revised it over the last seven or eight years.

It was originally an Access VB function.|||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)|||And by the way, Alanis Morissette is an American (as of today...).

http://apnews.excite.com/article/20050217/D88A92MG0.html|||i am flummoxed|||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.

i am flummoxedThat doesn't happen often. It must be some function, I'll have to look at it in more detail!

-PatP|||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 :)|||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 Murcilago, 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