MSSQL 首字母替换成大写字母

时间:2021-07-23 00:40:59

--使用程序块 

-->Title:生成測試數據 
-->Author:wufeng4552 
-->Date :2009-09-21 13:40:59 
declare @s varchar(8000) 
set @s=lower(@@version) 
select @s 
/* 
microsoft sql server 2005 - 9.00.4035.00 (intel x86) 
nov 24 2008 13:01:59 
copyright (c) 1988-2005 microsoft corporation 
enterprise edition on windows nt 5.2 (build 3790: service pack 2) 


(1 個資料列受到影響) 
*/ 
declare @i int,@j int 
select @i=1,@j=len(@j) 
while charindex(' ',' '+@s,@i)>0 
begin 
set @I=charindex(' ',' '+@s,@i)+1 
if @i>@j continue 
set @s=stuff(@s,@i-1,1,upper(substring(@s,@i-1,1))) 
end 
select @s 
/* 
Microsoft Sql Server 2005 - 9.00.4035.00 (intel X86) 
nov 24 2008 13:01:59 
copyright (c) 1988-2005 Microsoft Corporation 
enterprise Edition On Windows Nt 5.2 (build 3790: Service Pack 2) 


(1 個資料列受到影響) 
*/ 

----使用函数 

-->Title:生成測試數據 
-->Author:wufeng4552 
-->Date :2009-09-21 13:40:59 
if object_id('F_split')is not null drop function dbo.F_split 
go 
create function F_split(@s nvarchar(1000)) 
returns nvarchar(1000) 
as 
begin 
declare @str nvarchar(1000),@split nvarchar(100) 
select @s=@s+' ',@str='' 
while charindex(' ',@s)>0 
begin 
set @split=left(@s,charindex(' ',@s)) 
set @str=@str+upper(left(@split,1))+right(@split,len(@split)) 
set @s=stuff(@s,1,charindex(char(32),@s),'') 
end 
return @str 
end 
go 
declare @s varchar(1000) 
set @s=lower(@@version) 
select dbo.F_split(@s) 
/* 
Microsoft Sql Server 2005 - 9.00.4035.00 (intel X86) 
nov 24 2008 13:01:59 
copyright (c) 1988-2005 Microsoft Corporation 
enterprise Edition On Windows Nt 5.2 (build 3790: Service Pack 2) 
*/ 

--3借住系統表,或臨時表 

-->Title:生成測試數據 
-->Author:wufeng4552 
-->Date :2009-09-21 13:40:59 
declare @str varchar(1000) 
select @str=char(32)+lower(@@version) 
select @str=replace(@str,char(32)+char(number),char(32)+char(number)) 
from master..spt_values 
where type='p' and number between 65 and 90 
select stuff(@str,1,1,'') 
/* 
Microsoft Sql Server 2005 - 9.00.4035.00 (intel X86) 
nov 24 2008 13:01:59 
copyright (c) 1988-2005 Microsoft Corporation 
enterprise Edition On Windows Nt 5.2 (build 3790: Service Pack 2) 


(1 個資料列受到影響) 

*/