先创建一函数 create function fn_name(@str varchar(8000)) returns varchar(8000) as begin declare @i int declare @rstr varchar(8000) set @i=1 set @rstr='' while(@i<len(@str)) begin if(@i>1) if(substring(@str,@i-1,1)='') set @rstr=@rstr+upper(substring(@str,@i,1)) else set @rstr=@rstr+substring(@str,@i,1) else set @rstr=@rstr+upper(substring(@str,@i,1)) set @i=@i+1 end return @rstr end 然后调用select dbo.fn_name(字段名) from 表
--自定义函数,将每个单词的首字母转换成大写 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_myupper]') and xtype in (N'FN', N'IF', N'TF')) drop function [dbo].[f_myupper] GOcreate function f_myupper(@s varchar(8000)) returns varchar(8000) as begin declare @ch varchar(1),@i int,@j int,@len int select @i=1,@j=1,@len=len(@s) while @j<>0 select @ch=upper(substring(@s,@i,1)) ,@s=stuff(@s,@i,1,@ch) ,@j=charindex(' ',substring(@s,@i,8000)) ,@i=@i+@j return(@s) end go--调用示例 select dbo.f_myupper('a the a')
--如果只是处理变量,可以直接用下面的语句declare @s varchar(8000) set @s='a the aa'declare @ch varchar(1),@i int,@j int,@len int select @i=1,@j=1,@len=len(@s) while @j<>0 select @ch=upper(substring(@s,@i,1)) ,@s=stuff(@s,@i,1,@ch) ,@j=charindex(' ',substring(@s,@i,8000)) ,@i=@i+@jselect 处理结果=@s
create function fn_name(@str varchar(8000))
returns varchar(8000)
as
begin
declare @i int
declare @rstr varchar(8000)
set @i=1
set @rstr=''
while(@i<len(@str))
begin
if(@i>1)
if(substring(@str,@i-1,1)='')
set @rstr=@rstr+upper(substring(@str,@i,1))
else
set @rstr=@rstr+substring(@str,@i,1)
else
set @rstr=@rstr+upper(substring(@str,@i,1))
set @i=@i+1
end
return @rstr
end
然后调用select dbo.fn_name(字段名) from 表
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_myupper]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_myupper]
GOcreate function f_myupper(@s varchar(8000))
returns varchar(8000)
as
begin
declare @ch varchar(1),@i int,@j int,@len int
select @i=1,@j=1,@len=len(@s)
while @j<>0
select @ch=upper(substring(@s,@i,1))
,@s=stuff(@s,@i,1,@ch)
,@j=charindex(' ',substring(@s,@i,8000))
,@i=@i+@j
return(@s)
end
go--调用示例
select dbo.f_myupper('a the a')
set @s='a the aa'declare @ch varchar(1),@i int,@j int,@len int
select @i=1,@j=1,@len=len(@s)
while @j<>0
select @ch=upper(substring(@s,@i,1))
,@s=stuff(@s,@i,1,@ch)
,@j=charindex(' ',substring(@s,@i,8000))
,@i=@i+@jselect 处理结果=@s