--辅助表

create table a (

a int )

declare @b int

set @b=1

while @b<=30

begin

insert into a values(@b)

set @b=@b+1

end;

go

--表数据

declare @a table( a varchar(20),id int)

insert into @a select 'abc',1

insert into @a select 'abc bcd',2

insert into @a select 'abc bcd efg',3

insert into @a select 'abc bcd efg hif',4

--结果

select  id,(select a+' ' from

(select upper(left (substring(ab.a1,a.a,charindex(' ',ab.a1+' ',a.a)-a.a),1))+

right(substring(ab.a1,a.a,charindex(' ',ab.a1+' ',a.a)-a.a),len(substring(ab.a1,a.a,charindex(' ',ab.a1+' ',a.a)-a.a))-1) as a,id

 from a,(select a+' ' as a1,id from @a ) as ab where a.a<=len(ab.a1) and substring(' '+ab.a1,a.a,1)=' ') as a2

where a2.id=a1.id for xml path('')) as a from (select distinct id from @a) a1;

id          a

1           Abc

2           Abc Bcd

3           Abc Bcd Efg

4           Abc Bcd Efg Hif

(4 行受影响)

有点复杂了。