declare @s varchar(20)
set @s='new york'
select stuff(stuff(LOWER(@s),1,1,UPPER(left(@s,1))),5,1,UPPER(substring(@s,5,1)))New York
set @s='new york'
select stuff(stuff(LOWER(@s),1,1,UPPER(left(@s,1))),5,1,UPPER(substring(@s,5,1)))New York
------
abc(1 行受影响)
returns varchar(100)
as
begin
declare @i int
declare @j int
set @I = len(@s)
if @i < 2 return ''
set @J = 1
while (@j < @i)
begin
if substring(@s,@j,1) = ' '
set @j = @J + 2
else
set @j = @J + 1
set @s = replace(@s,substring(@s,@j,1),lower(substring(@s,@j,1)))
endreturn @s
end
godeclare @s varchar(10)set @s = 'NEW YORK'
select dbo.f_u( @s)
drop function f_u
/*(
----------------------------------------------------------------------------------------------------
New York(所影响的行数为 1 行)
*/
这样的话就显示错误了 这该怎么办呢
Function GetStr(strIn As String) As StringGetStr = StrConv(strIn, vbProperCase)End Function
2.执行
DECLARE @object int,@hr int
DECLARE @g varchar(100)EXEC @hr=sp_oaCreate 'ClsStr.ClsFormat',@object out
EXEC @hr = sp_OAMethod @object, 'GetStr', @g out, 'my_server sql server new york'SELECT @g--result
/*
-------------------------------------------------------
My_server Sql Server New York(所影响的行数为 1 行)
*/
AS
begindeclare @having_error int,@i int,@j int,@ret varchar(25)
select @having_error=0,@ret='',@j=1if @having_error=0
begin
if @gkqc is null
set @having_error=1
endif @having_error=0
begin
set @i = len(@gkqc) --取全称长度 if @i < 2 --一位长度或者为空,不变
begin
set @ret= @gkqc
end
end--循环判断
while @j < @i
begin
if substring(@gkqc,@j,1) = ' ' OR substring(@gkqc,@j,1)='(' OR substring(@gkqc,@j,1)=','
set @j = @j + 2
else
set @j = @j + 1 set @gkqc = replace(@gkqc,substring(@gkqc,@j,1),lower(substring(@gkqc,@j,1)))
endset @ret=isnull(@gkqc,'')
return @ret
end
go
----------------------
select dbo.f_uppertolower('AABENRAA') aabenraa
select dbo.f_uppertolower('CDBENRAA') Cdbenraa
select dbo.f_uppertolower('BCBENRAA') bcbenraa
select dbo.f_uppertolower('DEBENRAA') Debenraa
可是我想问下为什么结果是不一样的呢
as
begin
declare @i int,@x int
set @i = len(@s)
set @x = 1
set @s = lower(@s)
while(@x<@i+1)
begin
if(@x =1 or substring(@s,@x-1,1)=' ' or substring(@s,@x-1,1)='(')
begin
if(@x>1 and @x<@i)
set @s=substring(@s,1,@x-1)+upper(substring(@s,@x,1))+substring(@s,@x+1,@i-@x)
else if(@x=1)
set @s=upper(substring(@s,@x,1))+substring(@s,@x+1,@i-@x)
else if(@x=@i)
set @s=substring(@s,1,@x-1)+upper(substring(@s,@x,1))
end
set @x=@x+1
end
return @s
endselect dbo.fnUppertolower('AABE NRAA') --Aabe Nraa
select dbo.fnUppertolower('AABE(NRAA)') --Aabe(Nraa)