Split data then insert - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Split data then insert

Author

Topic

bpsintl
Posting Yak Master

132 Posts

Posted - 2009-05-02 : 07:12:58

I have a members table that I have a field that stores the member's name. This is a single field. I have decided now that I need this field split into 2 new fields, firstname and lastname.

I will create these 2 new fields in the members table. What query can I run that will take the name field, split it by the space and insert the first part into the firstname field and the second part into the lastname field?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-05-02 : 07:59:54

Are you sure that your data is always as you have told?
What is about 'Alfred E. Neumann'?
Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-05-02 : 08:15:06

[code]
--DECLARE @MemberName VARCHAR(255)
--SET @MemberName = 'MyFirstName MyLastName'
--
--SELECT PARSENAME(REPLACE(@MemberName, ' ', '.'), 2) AS [FirstName],
-- PARSENAME(REPLACE(@MemberName, ' ', '.'), 1) AS [LastName]
--
--to do the update:
update members
set Firstname=PARSENAME(REPLACE(MemberName, ' ', '.'), 2),
Lastname=PARSENAME(REPLACE(MemberName, ' ', '.'), 1)
[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

F1nd1nfo
Starting Member

4 Posts

Posted - 2009-05-02 : 08:57:13

Hi There Rescuers :)

Can someone please help me with my request. I'm fairly new at this and require sql statement for the Amount of keys processed for every hour
between the Package_Start_Dte and Package_End_Dte. Your assistance much appreciated. Also, the average amount of keys in an hour as well as the keys processed for the day.

This is what the table looks like:
Package_Start_Dte Package_End_Dte Package_Exit_Error Process_Status
----------------------- ----------------------- --------- -----------------------------------
2007-12-03 18:22:31.910 2007-12-03 18:23:30.597 0 [Move] Successfull. Keys Moved: 2835
2007-12-03 18:17:31.867 2007-12-03 18:18:17.397 0 [Move] Successfull. Keys Moved: 2724
2007-12-03 18:12:32.010 2007-12-03 18:13:20.103 0 [Move] Successfull. Keys Moved: 2999
2007-12-03 18:07:32.310 2007-12-03 18:08:31.140 0 [Move] Successfull. Keys Moved: 3082
2007-12-03 18:02:31.940 2007-12-03 18:03:33.533 0 [Move] Successfull. Keys Moved: 2931
2007-12-03 17:57:32.553 2007-12-03 17:58:37.333 0 [Move] Successfull. Keys Moved: 3514
2007-12-03 17:52:33.150 2007-12-03 17:53:16.917 0 [Move] Successfull. Keys Moved: 3010
2007-12-03 17:47:32.470 2007-12-03 17:48:14.357 0 [Move] Successfull. Keys Moved: 2883

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-05-02 : 09:02:26

F1nd1nfo-

Don't hijack a thread with a different question, especially if you've already started another:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=124958

F1nd1nfo
Starting Member

4 Posts

Posted - 2009-05-02 : 09:06:01

I know i'm so sorry, just that i require assistance urgently. sorry for this, is it possible that one of you can help me. Thanks in advance and would be much appreciated :)

bpsintl
Posting Yak Master

132 Posts

Posted - 2009-05-02 : 09:54:48

I've just checked and I dont have any names like 'Alfred E. Neumann' but I do have a few that have middle names, such as Jo David Bloggs etc.

How could I alter the query to include those too?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-05-02 : 17:59:54

[code]
--DECLARE @MemberName VARCHAR(255)
--SET @MemberName = 'MyFirstName MidName MyLastName'
--
--SELECT isnull(PARSENAME(REPLACE(@MemberName, ' ', '.'), 3),'')+
-- isnull(' '+(PARSENAME(REPLACE(@MemberName, ' ', '.'), 2)),'') AS [FirstMidName],
-- PARSENAME(REPLACE(@MemberName, ' ', '.'), 1) AS [LastName]

update members
set Firstname=isnull(PARSENAME(REPLACE(MemberName, ' ', '.'), 3),'')+
isnull(' '+(PARSENAME(REPLACE(MemberName, ' ', '.'), 2)),''),
Lastname=PARSENAME(REPLACE(MemberName, ' ', '.'), 1)
[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

bpsintl
Posting Yak Master

132 Posts

Posted - 2009-05-03 : 08:48:37

Thanks for that!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources