Ms sql将首字母大写

最新推荐文章于 2021-09-14 09:20:25 发布

转载最新推荐文章于 2021-09-14 09:20:25 发布 · 144 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/1-Admin/p/6086807.html

Ms sql将首字母大写

--辅助表
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 行受影响)

有点复杂了。