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

My SQL server doesn't exist?

So here's what happened:
I was running out of space on my DB partition (there was a sudden surge in
DB size due to a lot of scanned documents being attached in a short period
of time). To free up space for a day or two while I waited for an
additional drive to add to the RAID set, I decided to temporarily move an
older, infrequently-accessed database to another volume. Here's what I did:
- I stopped the server and server agent services for the database I was
moving -- I'll call it Old-DB -- , then simply copied its entire MSSQL
folder, lock stock and barrel, to a network share on another server.
- When the new drive arrived a day later, I added it to the RAID set and
increased the size of the volume.
- I rebooted the SQL server for reasons unrelated to this issue. On
startup, it reported that a driver or service had failed because the Old-DB
had tried to start, but of course it's MSSQL folder wasn't there.
- I copied the MSSQL folder for Old-DB back to its original location on the
SQL server.
Now I get a 1053 error when trying to start the SQL Server, and a 1068 error
when trying to start the SQL Server Agent. Enterprise Manager now shows a
(local) database that's not started, with the message (Connection failed,
check SQL Server Registration Properties) under it.
If I understand correctly, my big worry shouldn't be about the data (which
is probably fine), but about the user logins. Is that right?
What's my next step to getting this DB back online? It's not urgent, but it
IS important that we retain or regain access to the historical data in this
DB.
Thanks in advance!
BJYou should perform a full system restore to recover to the state you were in
before you started moving files.
Then start again and do the job "correctly" i.e. by following the documented
procedures (see Books Online) for moving databases.
"Bryan L" <blinton.nospam@.connellinsurance.nospam.com> wrote in message
news:e3%235claNHHA.2232@.TK2MSFTNGP02.phx.gbl...
> So here's what happened:
> I was running out of space on my DB partition (there was a sudden surge in
> DB size due to a lot of scanned documents being attached in a short period
> of time). To free up space for a day or two while I waited for an
> additional drive to add to the RAID set, I decided to temporarily move an
> older, infrequently-accessed database to another volume. Here's what I
> did:
> - I stopped the server and server agent services for the database I was
> moving -- I'll call it Old-DB -- , then simply copied its entire MSSQL
> folder, lock stock and barrel, to a network share on another server.
> - When the new drive arrived a day later, I added it to the RAID set and
> increased the size of the volume.
> - I rebooted the SQL server for reasons unrelated to this issue. On
> startup, it reported that a driver or service had failed because the
> Old-DB had tried to start, but of course it's MSSQL folder wasn't there.
> - I copied the MSSQL folder for Old-DB back to its original location on
> the SQL server.
> Now I get a 1053 error when trying to start the SQL Server, and a 1068
> error when trying to start the SQL Server Agent. Enterprise Manager now
> shows a (local) database that's not started, with the message (Connection
> failed, check SQL Server Registration Properties) under it.
> If I understand correctly, my big worry shouldn't be about the data (which
> is probably fine), but about the user logins. Is that right?
> What's my next step to getting this DB back online? It's not urgent, but
> it IS important that we retain or regain access to the historical data in
> this DB.
> Thanks in advance!
> BJ
>|||I have a full disk image of the SQL server that was taken just prior to
retiring the database and migrating to the new version of the application.
I'll restore that image to temporary hardware, verify that I can access the
DB, and then use Books Online to lookup and follow an accepted procedure for
moving or restoring that database to a new server.
Thanks for cutting to the chase. :-)
BJ
"Mark Yudkin" <DoNotContactMe@.boingboing.org> wrote in message
news:uwx0LO8NHHA.4172@.TK2MSFTNGP04.phx.gbl...
> You should perform a full system restore to recover to the state you were
> in before you started moving files.
> Then start again and do the job "correctly" i.e. by following the
> documented procedures (see Books Online) for moving databases.
> "Bryan L" <blinton.nospam@.connellinsurance.nospam.com> wrote in message
> news:e3%235claNHHA.2232@.TK2MSFTNGP02.phx.gbl...
>> So here's what happened:
>> I was running out of space on my DB partition (there was a sudden surge
>> in DB size due to a lot of scanned documents being attached in a short
>> period of time). To free up space for a day or two while I waited for an
>> additional drive to add to the RAID set, I decided to temporarily move an
>> older, infrequently-accessed database to another volume. Here's what I
>> did:
>> - I stopped the server and server agent services for the database I was
>> moving -- I'll call it Old-DB -- , then simply copied its entire MSSQL
>> folder, lock stock and barrel, to a network share on another server.
>> - When the new drive arrived a day later, I added it to the RAID set and
>> increased the size of the volume.
>> - I rebooted the SQL server for reasons unrelated to this issue. On
>> startup, it reported that a driver or service had failed because the
>> Old-DB had tried to start, but of course it's MSSQL folder wasn't there.
>> - I copied the MSSQL folder for Old-DB back to its original location on
>> the SQL server.
>> Now I get a 1053 error when trying to start the SQL Server, and a 1068
>> error when trying to start the SQL Server Agent. Enterprise Manager now
>> shows a (local) database that's not started, with the message (Connection
>> failed, check SQL Server Registration Properties) under it.
>> If I understand correctly, my big worry shouldn't be about the data
>> (which is probably fine), but about the user logins. Is that right?
>> What's my next step to getting this DB back online? It's not urgent, but
>> it IS important that we retain or regain access to the historical data in
>> this DB.
>> Thanks in advance!
>> BJ
>

My SQL server doesn't exist?

So here's what happened:
I was running out of space on my DB partition (there was a sudden surge in
DB size due to a lot of scanned documents being attached in a short period
of time). To free up space for a day or two while I waited for an
additional drive to add to the RAID set, I decided to temporarily move an
older, infrequently-accessed database to another volume. Here's what I did:
- I stopped the server and server agent services for the database I was
moving -- I'll call it Old-DB -- , then simply copied its entire MSSQL
folder, lock stock and barrel, to a network share on another server.
- When the new drive arrived a day later, I added it to the RAID set and
increased the size of the volume.
- I rebooted the SQL server for reasons unrelated to this issue. On
startup, it reported that a driver or service had failed because the Old-DB
had tried to start, but of course it's MSSQL folder wasn't there.
- I copied the MSSQL folder for Old-DB back to its original location on the
SQL server.
Now I get a 1053 error when trying to start the SQL Server, and a 1068 error
when trying to start the SQL Server Agent. Enterprise Manager now shows a
(local) database that's not started, with the message (Connection failed,
check SQL Server Registration Properties) under it.
If I understand correctly, my big worry shouldn't be about the data (which
is probably fine), but about the user logins. Is that right?
What's my next step to getting this DB back online? It's not urgent, but it
IS important that we retain or regain access to the historical data in this
DB.
Thanks in advance!
BJ
I have a full disk image of the SQL server that was taken just prior to
retiring the database and migrating to the new version of the application.
I'll restore that image to temporary hardware, verify that I can access the
DB, and then use Books Online to lookup and follow an accepted procedure for
moving or restoring that database to a new server.
Thanks for cutting to the chase. :-)
BJ
"Mark Yudkin" <DoNotContactMe@.boingboing.org> wrote in message
news:uwx0LO8NHHA.4172@.TK2MSFTNGP04.phx.gbl...
> You should perform a full system restore to recover to the state you were
> in before you started moving files.
> Then start again and do the job "correctly" i.e. by following the
> documented procedures (see Books Online) for moving databases.
> "Bryan L" <blinton.nospam@.connellinsurance.nospam.com> wrote in message
> news:e3%235claNHHA.2232@.TK2MSFTNGP02.phx.gbl...
>

My SQL server doesn't exist?

So here's what happened:
I was running out of space on my DB partition (there was a sudden surge in
DB size due to a lot of scanned documents being attached in a short period
of time). To free up space for a day or two while I waited for an
additional drive to add to the RAID set, I decided to temporarily move an
older, infrequently-accessed database to another volume. Here's what I did:
- I stopped the server and server agent services for the database I was
moving -- I'll call it Old-DB -- , then simply copied its entire MSSQL
folder, lock stock and barrel, to a network share on another server.
- When the new drive arrived a day later, I added it to the RAID set and
increased the size of the volume.
- I rebooted the SQL server for reasons unrelated to this issue. On
startup, it reported that a driver or service had failed because the Old-DB
had tried to start, but of course it's MSSQL folder wasn't there.
- I copied the MSSQL folder for Old-DB back to its original location on the
SQL server.
Now I get a 1053 error when trying to start the SQL Server, and a 1068 error
when trying to start the SQL Server Agent. Enterprise Manager now shows a
(local) database that's not started, with the message (Connection failed,
check SQL Server Registration Properties) under it.
If I understand correctly, my big worry shouldn't be about the data (which
is probably fine), but about the user logins. Is that right?
What's my next step to getting this DB back online? It's not urgent, but it
IS important that we retain or regain access to the historical data in this
DB.
Thanks in advance!
BJYou should perform a full system restore to recover to the state you were in
before you started moving files.
Then start again and do the job "correctly" i.e. by following the documented
procedures (see Books Online) for moving databases.
"Bryan L" <blinton.nospam@.connellinsurance.nospam.com> wrote in message
news:e3%235claNHHA.2232@.TK2MSFTNGP02.phx.gbl...
> So here's what happened:
> I was running out of space on my DB partition (there was a sudden surge in
> DB size due to a lot of scanned documents being attached in a short period
> of time). To free up space for a day or two while I waited for an
> additional drive to add to the RAID set, I decided to temporarily move an
> older, infrequently-accessed database to another volume. Here's what I
> did:
> - I stopped the server and server agent services for the database I was
> moving -- I'll call it Old-DB -- , then simply copied its entire MSSQL
> folder, lock stock and barrel, to a network share on another server.
> - When the new drive arrived a day later, I added it to the RAID set and
> increased the size of the volume.
> - I rebooted the SQL server for reasons unrelated to this issue. On
> startup, it reported that a driver or service had failed because the
> Old-DB had tried to start, but of course it's MSSQL folder wasn't there.
> - I copied the MSSQL folder for Old-DB back to its original location on
> the SQL server.
> Now I get a 1053 error when trying to start the SQL Server, and a 1068
> error when trying to start the SQL Server Agent. Enterprise Manager now
> shows a (local) database that's not started, with the message (Connection
> failed, check SQL Server Registration Properties) under it.
> If I understand correctly, my big worry shouldn't be about the data (which
> is probably fine), but about the user logins. Is that right?
> What's my next step to getting this DB back online? It's not urgent, but
> it IS important that we retain or regain access to the historical data in
> this DB.
> Thanks in advance!
> BJ
>|||I have a full disk image of the SQL server that was taken just prior to
retiring the database and migrating to the new version of the application.
I'll restore that image to temporary hardware, verify that I can access the
DB, and then use Books Online to lookup and follow an accepted procedure for
moving or restoring that database to a new server.
Thanks for cutting to the chase. :-)
BJ
"Mark Yudkin" <DoNotContactMe@.boingboing.org> wrote in message
news:uwx0LO8NHHA.4172@.TK2MSFTNGP04.phx.gbl...
> You should perform a full system restore to recover to the state you were
> in before you started moving files.
> Then start again and do the job "correctly" i.e. by following the
> documented procedures (see Books Online) for moving databases.
> "Bryan L" <blinton.nospam@.connellinsurance.nospam.com> wrote in message
> news:e3%235claNHHA.2232@.TK2MSFTNGP02.phx.gbl...
>