sql 自定义函数
--检查函数是否存在
if exists (select * from dbo.sysobjects where id = object_id(N'dbo.pTitleCase') and xtype in (N'FN', N'IF', N'TF'))
drop function dbo.pTitleCase ;
go
--创建函数
create function dbo.pTitleCase(@strIn nvarchar(Max))
returns nvarchar(Max)
as
begin;
declare
@strOut nvarchar(max),
@currentPostion int,
@nextSpace int,
@currentWord nvarchar(max),
@strLen int,
@lastWord bit;
set @nextSpace = 1;
set @currentPostion =1;
set @strOut ='';
set @strLen=Len(@strIn);
set @lastWord=0;
while @lastWord=0
begin;
set @nextSpace=CharIndex( ' ',@strIn,@currentPostion+1)
if @nextSpace=0 --no more spaces found
begin;
set @nextSpace=@strLen;
set @lastWord=1;
end;
set @currentWord = Upper(substring(@strIn,@currentPostion,1));
set @currentWord = @currentWord + lower(substring(@strin,@currentPostion+1,@nextSpace-@currentPostion));
set @strOut=@strOut+@currentWord;
set @currentPostion=@nextSpace+1;
end;
return @strOut;
end;
go
--执行函数
SELECT [Demo].[dbo].[pTitleCase] ( 'one TWO tHrEe')
go