2007年8月24日星期五

Implement INITCAP function in SQL Server

To convert a normal string into initial captial form, SQL Server does not provide the same function as well as Oracle. However we can implement by ourselves.
For example:
create function initcap (@text varchar(4000))
returns varchar(4000)
as
begin
declare @counter int,
@length int,
@char char(1),
@textnew varchar(4000)

set @text = rtrim(@text)
set @text = lower(@text)
set @length = len(@text)
set @counter = 1

set @text = upper(left(@text, 1) ) + right(@text, @length - 1)

while @counter <> @length --+ 1
begin
select @char = substring(@text, @counter, 1)
if @char = space(1) or @char = '_' or @char = ',' or @char = '.' or @char = '\' or @char = '/' or @char = '(' or @char = ')' or @char='"' or @char='''' or @char=':' or @char='?' or @char='"'
begin
set @textnew = left(@text, @counter) + upper(substring(@text, @counter+1, 1)) + right(@text, (@length - @counter) - 1)
set @text = @textnew
end

set @counter = @counter + 1
end

return @text
end

2 条评论:

wynano 说...

Hi..i would to know you..you're so good in sql..btw, Im an IT student..i like your blog cuz you can help someone who really needs code badly..more power to you!^^

angelie 说...

hey!... thanks for the codes...
you really helped me alot...
thank you for this