在sql中如何把一列的值拆分成多列,求高手指教..

[Bon_soir]

如下所示：
我现在有一个字段ShipName，它的值为Joel Earl Johnson。

我需要把这一列值拆分成firstname，middlename ，lastname
这三列.Joel为firstname.Earl为middlename Johnson为lastname.
如果用sql的话，我该怎么去拆分它们...跪求高手帮我解决这个难题...谢谢

[飘零一叶]

create table tb
(
  name varchar(100)
)
insert into tb
select 'Joel Earl Johnson'

select parsename(replace(name,' ','.'),3) firstname
,parsename(replace(name,' ','.'),2) middlename
,parsename(replace(name,' ','.'),1) lastname
from tb
/*
----------------------
firstname	middlename	lastname
Joel	Earl	Johnson
*/

[dawugui]

[Quote=引用楼主 bon_soir 的回复:]
如下所示：
我现在有一个字段ShipName，它的值为Joel Earl Johnson。

我需要把这一列值拆分成firstname，middlename ，lastname
这三列.Joel为firstname.Earl为middlename Johnson为lastname.
如果用sql的话，我该怎么去拆分它们...跪求高手帮我解决这个难题...谢谢
[/Quote]
固定的三列吗?

create table tb
(
  name varchar(100)
)
insert into tb
select 'Joel Earl Johnson'


select left(name, charindex(' ',name) - 1) firstname,
       substring(name , charindex(' ',name) + 1 , charindex(' ',name , charindex(' ',name) + 1) - charindex(' ',name) ) middlename,
       substring(name , charindex(' ',name , charindex(' ',name) + 1) + 1,len(name)) lastname
from tb

drop table tb

/*
firstname                                                                                            middlename                                                                                           lastname                                                                                             
---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- 
Joel                                                                                                 Earl                                                                                                 Johnson

（所影响的行数为 1 行）
*/

[-晴天]

create table tb(ID int,ShipName nvarchar(20))
insert into tb select 1,'Joel Earl Johnson'
go
;with cte as(
select id,convert(nvarchar(20),left(ShipName,charindex(' ',ShipName+' ')-1))ShipName,convert(nvarchar(20),right(ShipName+' ',len(ShipName)-charindex(' ',ShipName+' ')+1))RShipName from tb
union all
select id,convert(nvarchar(20),left(RShipName,charindex(' ',RShipName)-1))ShipName,convert(nvarchar(20),right(RShipName,len(RShipName)-charindex(' ',RShipName)+1))RShipName from cte where len(RShipName)>1
)select id,ShipName from cte order by id
go
drop table tb
/*
id          ShipName
----------- --------------------
1           Joel
1           Earl
1           Johnson

(3 行受影响)

*/

[-晴天]

create table tb(ID int,ShipName nvarchar(20))
insert into tb select 1,'Joel Earl Johnson'
insert into tb select 2,'doijadf ijoadso jii'
insert into tb select 3,'faweu fasad'
insert into tb select 4,'fawe'
go
;with cte as(
select id,1 as flg,convert(nvarchar(20),left(ShipName,charindex(' ',ShipName+' ')-1))ShipName,convert(nvarchar(20),right(ShipName+' ',len(ShipName)-charindex(' ',ShipName+' ')+1))RShipName from tb
union all
select id,flg+1 as flg,convert(nvarchar(20),left(RShipName,charindex(' ',RShipName)-1))ShipName,convert(nvarchar(20),right(RShipName,len(RShipName)-charindex(' ',RShipName)+1))RShipName from cte where len(RShipName)>1
)
select a.id,a.ShipName as firstname,isnull(b.shipname,'') as middlename,isnull(c.shipname,'') as lastname from cte a left join cte b on a.id=b.id and a.flg=b.flg-1 left join cte c on b.id=c.id and b.flg=c.flg-1
where a.flg=(case when c.flg is not null then c.flg-2 else a.flg end) and a.flg=1
go
drop table tb
/*
id          firstname            middlename           lastname
----------- -------------------- -------------------- --------------------
1           Joel                 Earl                 Johnson
2           doijadf              ijoadso              jii
3           faweu                fasad                
4           fawe                                      

(4 行受影响)
*/

[一品梅]

晴天考虑的更全面，佩服。。。

[xiaoyehua]

[Quote=引用 1 楼 dlut_liuq 的回复:]
select parsename(replace(name,' ','.'),3) firstname
,parsename(replace(name,' ','.'),2) middlename
,parsename(replace(name,' ','.'),1) lastname
from tb
[/Quote]
在这题里，我很欣赏2楼的思维。PFPF

[Bon_soir]

[Quote=引用 1 楼 dlut_liuq 的回复:]
SQL code
create table tb
(
name varchar(100)
)
insert into tb
select 'Joel Earl Johnson'

select parsename(replace(name,' ','.'),3) firstname
,parsename(replace(name,' ','.'),2) middlename
……
[/Quote]
ShipName里面的值都是不固定的..
像这样子的:

[Bon_soir]

[Quote=引用 2 楼 dawugui 的回复:]
引用楼主 bon_soir 的回复:
如下所示：
我现在有一个字段ShipName，它的值为Joel Earl Johnson。

我需要把这一列值拆分成firstname，middlename ，lastname
这三列.Joel为firstname.Earl为middlename Johnson为lastname.
如果用sql的话，我该怎么去拆分它们...跪求高手帮我解决这个难题……
[/Quote]

里面三列的值都是不固定的...现在需要的是把有单独的大写英文字母的提炼出来为middlename，然后剩下的就是firstname与lastname。如（Andrew S. Arriola；Bruce W. Adams；Samuel J. Barther ） 这样的，就需要把第一个的S,第二个的W,第三个的J提出来为middlename...里面有的还有空格,所以比较麻烦...表名为PT_Academy_5

[joyhen]

二楼不错

[linfei_z]

哇。高人在这里啊。找到了。正好需要。