Jeff Moden,

Wonderful note about set statistics time! Never heard of it.

"Try to beat it using any form of "all in one query" code."

Here you go!

Sample Data (one million rows of strings, 1 to 10 word, 2-16 word length)

use tempdb;

go

if object_id('dbo.TestTable') is not null drop table dbo.TestTable;

go

select top(1000000) t.rnd_txt as s

into dbo.TestTable

from

(select char(convert(int,rand(checksum(newid()))*26+97))) c (rnd_char)

cross apply (select convert(int,rand(checksum(newid()))*15+2)) l(rnd_len)

cross apply (select replicate(c.rnd_char,l.rnd_len)+' ') w(rnd_word)

cross apply (select convert(int,rand(checksum(newid()))*10+1)) wc(rnd_wcount)

cross apply (select replicate(w.rnd_word,wc.rnd_wcount)+' ') t(rnd_txt)

cross join sys.all_columns o1

cross join sys.all_columns o2

;

here is function:

create FUNCTION dbo.InitialCapFaster(@String VARCHAR(8000))

RETURNS table

AS

return

with

c as

(

select

String = STUFF(LOWER(@String),1,1,UPPER(LEFT(@String,1))) COLLATE Latin1_General_Bin,

Position = PATINDEX('%[^A-Za-z''][a-z]%',@String COLLATE Latin1_General_Bin),

Step = 1

union all

select

String = STUFF(c.String,c.Position,2,UPPER(SUBSTRING(c.String,c.Position,2))) COLLATE Latin1_General_Bin,

Position = PATINDEX('%[^A-Za-z''][a-z]%',c.String COLLATE Latin1_General_Bin),

Step = c.Step+1

from c

where c.Position > 0

)

select top(1) String from c order by Step desc

go

Here is test

declare @s-2 varchar(8000);

declare @ds datetime = getdate();

select @s-2 = dbo.InitialCap(s) from testtable;

print 'InitialCap: ' + convert(varchar(10),datediff(ms,@ds,getdate())) + ' ms'

go

declare @s-2 varchar(8000);

declare @ds datetime = getdate();

select @s-2 = f.String from dbo.testtable cross apply dbo.InitialCapFaster(s) f;

print 'InitialCapFaster: ' + convert(varchar(10),datediff(ms,@ds,getdate())) + ' ms'

go

print @@version

Results

InitialCap: 25533 ms

InitialCapFaster: 12856 ms

Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (Intel X86)

Apr 2 2010 15:53:02

Copyright (c) Microsoft Corporation

Enterprise Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)

Almost 50% faster. I ran several tests, playing with test data.

Depending on row count, words count and words length I got 30%-60% percents faster.

But if there are not so many rows, 10 000 for example, than InitialCap wins, about 250 ms vs 600 ms in my experiments.

And 35 000 rows id the threshold where both functions are almost equal InitialCap: 893 ms vs InitialCapFaster: 860 ms

Try it on your data.

All the tests were made on my local machine with 2008R2 sql.