full name into four columns - 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)

full name into four columns

Author

Topic

amanda
Starting Member

9 Posts

Posted - 2011-02-02 : 06:29:44

Dear Experts
I made big mistake before and I’m trying to correct it now
I have an employee table with one Full-Name column

Now, I’m re-designing and I should put the name in four first, second, third last name columns
How can I transfer the data from the old table to the new table?
Thank you in advance

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-02 : 06:50:19

As far as the question "How can I transfer the data from the old table to the new table?" is concerned. Following is the query which can perform this task

INSERT into NewTable (Col1,Col2,...,ColN)
Select Col1,Col2,...,ColN from OldTable

as simple as that

However if this is not helping you then explain more about the first, second, third and last name columns e.g. What are your requirements as the full name should be transferred into the 4 part naming structure? what are the business constraints etc

Also if the problems are specific to any script then post the script/query and the problems/errors etc you are facing with it ...

cheers
MIK

eralper
Yak Posting Veteran

66 Posts

Posted - 2011-02-02 : 09:21:13

Hello Amanda,

You should split the fullname then update your table to set the splitted pieces to the new columns

Here is a sample script

What is important here is the sql string split function.
I used the second function whose codes given at [url]http://www.kodyaz.com/articles/sql-server-t-sql-split-function.aspx[/url]

You can use other functions as well, but it is better if it returns an auto increment integer value as the above example


/*
create table SampleSplitTable (
	Id int identity(1,1),
	Fullname varchar(100),
	name1 varchar(25),
	name2 varchar(25),
	name3 varchar(25),
	name4 varchar(25)
)
*/
--insert into SampleSplitTable (fullname) values ('Eralper Yilmaz'), ('Junior Kit Walker')
select * from SampleSplitTable

;with cte as (
	select
		t.id,
		t.fullname,
		name1 = case when s.id = 1 then s.val else null end,
		name2 = case when s.id = 2 then s.val else null end,
		name3 = case when s.id = 3 then s.val else null end,
		name4 = case when s.id = 4 then s.val else null end
	from SampleSplitTable t
	cross apply dbo.split(t.fullname, ' ') s
), cte2 as (
select 
	id,
	fullname,
	name1 = MAX(name1),
	name2 = MAX(name2),
	name3 = MAX(name3),
	name4 = MAX(name4)
from cte 
group by id, fullname
)
update SampleSplitTable
set
	name1 = cte2.name1,
	name2 = cte2.name2,
	name3 = cte2.name3,
	name4 = cte2.name4
from SampleSplitTable t
inner join cte2 on cte2.id = t.id


select * from SampleSplitTable

I hope that helps,

-------------
Eralper
http://www.kodyaz.com

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-02-02 : 09:21:40

You can split four names using parsename function

Madhivanan

Failing to plan is Planning to fail

eralper
Yak Posting Veteran

66 Posts

Posted - 2011-02-02 : 09:44:02

Hi Madhivanan,
The problem with ParseName function is that it numbers from the end of the string

Here is an example:

select 
	parsename(replace('eralper yilmaz',' ','.'),1),
	parsename(replace('eralper yilmaz',' ','.'),2),
	parsename(replace('eralper yilmaz',' ','.'),3),
	parsename(replace('eralper yilmaz',' ','.'),4)

I'ld expect such a function to name "eralper" as 1 and "yilmaz" as 2

-------------
Eralper
http://www.kodyaz.com

mandm
Posting Yak Master

120 Posts

Posted - 2011-02-02 : 13:35:09

One other piece of advice. Take the time to find out all the variations that the fullname column can contain. Without seeing any sample data you could very well have "Mr John Jones", "John P Jones", "John Jones Jr" etc.

All of these variations will change the positions returned in the split functions so you may not be able to count on a certain index number always containing first name for example.

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2011-02-03 : 10:37:48

This function will split or reformat most western-style names:

http://dl.dropbox.com/u/2507186/Functions/FormatName.sql

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________

amanda
Starting Member

9 Posts

Posted - 2011-02-07 : 11:11:26

thank you all for your reply

I'm using PARSName function,
I appreacte your help alot

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources