# How to Capitalize One or More Words

• ### Question

• What is the best way to get Philadelphia and New York City in the following sample:

```DECLARE @Word nvarchar(32) = 'philadeLphia'
DECLARE @Phrase nvarchar(32) = 'new yorK city'```
Thanks.
Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
Sunday, November 21, 2010 7:40 AM

• Take a look at this function here

Proper case T-SQL function

Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog
• Marked as answer by Friday, December 3, 2010 12:07 AM
Sunday, November 21, 2010 8:13 PM
• In UDF form:

```CREATE FUNCTION dbo.fn_Capitalize01
(
@str Varchar(100)
) RETURNS Varchar(100)
AS
BEGIN
DECLARE @i INTEGER
SET @i=1
WHILE @i <= LEN(@str) BEGIN
SET @str = STUFF(@str,@i,1,CASE WHEN SUBSTRING(@str,@i-1,1)<>' ' THEN LOWER(SUBSTRING(@str,@i,1)) ELSE UPPER(SUBSTRING(@str,@i,1)) END)
SET @i = @i + 1
END
RETURN @str
END
GO

CREATE FUNCTION dbo.fn_Capitalize02
(
@str Varchar(100)
) RETURNS Varchar(100)
AS
BEGIN
DECLARE @res Varchar(100)
DECLARE @i INTEGER
DECLARE @c Char
DECLARE @cap Bit
SET @i=1
SET @cap=1
SET @res=''

WHILE @i <= LEN(@str) BEGIN
SET @c = LOWER(SUBSTRING(@str,@i,1))
IF @cap=1
SET @c = UPPER(@c)
IF @c = ' '
SET @cap=1
ELSE
SET @cap=0
SET @res = @res + @c
SET @i = @i + 1
END
RETURN @res
END
GO

```

Michael Asher
• Marked as answer by Friday, December 3, 2010 12:08 AM
Wednesday, November 24, 2010 9:58 PM
• You know what I think is the funniest part about all of this? At the time I wrote that function, I was eyebrow deep in to collations and on a lark, decided to see how the PatIndex function worked with different collations.  Whenever I write code, I always keep performance in mind.  In this case, I wasn't specifically trying to make it the fastest performing.  I was primarily concerned with accuracy, coupled with a clever method for reducing the number of loops.
• Marked as answer by Friday, December 3, 2010 4:44 AM
Friday, December 3, 2010 12:12 AM

### All replies

• A bit clumsy, but it does the trick:

```SET NOCOUNT ON

DECLARE @str Varchar(100)
DECLARE @res Varchar(100)
DECLARE @i INTEGER
DECLARE @c Char
DECLARE @cap Bit

SET @str = 'new yOrK cItY'

SET @i=1
SET @cap=1
SET @res=''

WHILE @i <= LEN(@str) BEGIN
SET @c = LOWER(SUBSTRING(@str,@i,1))
IF @cap=1
SET @c = UPPER(@c)
IF @c = ' '
SET @cap=1
ELSE
SET @cap=0
SET @res = @res + @c
SET @i = @i + 1
END

PRINT @res```

Michael Asher
Sunday, November 21, 2010 7:53 AM
• A more succinct version:

```DECLARE @str Varchar(100)
DECLARE @i INTEGER

SET @str = 'nEw yOrK cItY'
SET @i=1

WHILE @i <= LEN(@str) BEGIN
SET @str = STUFF(@str,@i,1,CASE WHEN SUBSTRING(@str,@i-1,1)<>' ' THEN LOWER(SUBSTRING(@str,@i,1)) ELSE UPPER(SUBSTRING(@str,@i,1)) END)
SET @i = @i + 1
END

PRINT @str
```

Michael Asher
Sunday, November 21, 2010 8:03 AM
• My two cents

CREATE FUNCTION dbo.fn_capitalize
(
@str AS nvarchar(100)
)
RETURNS nvarchar(100)
AS
BEGIN

DECLARE
@ret_str AS varchar(100),
@pos AS int,
@len AS int

SELECT
@ret_str = N' ' + LOWER(@str),
@pos = 1,
@len = LEN(@str) + 1

WHILE @pos > 0 AND @pos < @len
BEGIN
SET @ret_str = STUFF(@ret_str,
@pos + 1,
1,
UPPER(SUBSTRING(@ret_str,@pos + 1, 1)))
SET @pos = CHARINDEX(N' ', @ret_str, @pos + 1)
END

RETURN RIGHT(@ret_str, @len - 1)

END

------
SELECT dbo.fn_capitalize('new yorK city')

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
Sunday, November 21, 2010 8:44 AM
• There is no better way. We should write some kind of loopy code. But By using Regular Expression,I think we can replace the first character after every space with its upper character. But as RegEx is not much supported in t-sql, Probably a CLR function will perform more better here...
Sunday, November 21, 2010 8:53 AM
• Is bitwise operation a possibility (for one English word)?
Kalman Toth, SQL Server & Business Intelligence Training; Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016
• Edited by Friday, November 10, 2017 9:55 AM
Sunday, November 21, 2010 12:21 PM
• Take a look at this function here

Proper case T-SQL function

Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog
• Marked as answer by Friday, December 3, 2010 12:07 AM
Sunday, November 21, 2010 8:13 PM
• my version.

```IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[StringTitleCase]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[TitleCase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[TitleCase]
(@Input NVARCHAR(4000))
RETURNS TABLE
AS
RETURN
SELECT
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE
(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE
(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE
(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE
(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE
(REPLACE(STUFF(@Input,1,1,UPPER(SUBSTRING(@Input,1,1)))
,' a',' A'),' b',' B'),' c',' C')
,' d',' D'),' e',' E'),' f',' F'),' g',' G')
,' h',' H'),' i',' I'),' j',' J'),' k',' K')
,' l',' L'),' m',' M'),' n',' N'),' o',' O')
,' p',' P'),' q',' Q'),' r',' R'),' s',' S')
,' t',' T'),' u',' U'),' v',' V'),' w',' W')
,' x',' X'),' y',' Y'),' z',' Z') [Output]

GO
```

Jon
Monday, November 22, 2010 9:01 AM
• Hello an other version

```DECLARE @Word nvarchar(32) = 'new yorK city'
DECLARE @Word2 nvarchar(32) = @Word

select @Word = STUFF(LOWER(@Word),1,1,upper(left(@word,1)))

declare @i int, @j int
set @i = CHARINDEX(' ',@Word2,1)

while @i > 0
begin
select @j = @i, @i = @i + 1
select @Word = STUFF(@word,@i,1,upper(SUBSTRING(@word,@i,1)))

select @Word2 = STUFF(@word2,@j,1,'-')

select @i = CHARINDEX(' ',@Word2,1)
end

select @Word
```

SQL Server and T-SQL Tutorials
My Personal Site
Our true mentor in life is science
Monday, November 22, 2010 9:22 AM
• Superb collection! Will start testing soon....
Kalman Toth, SQL Server & Business Intelligence Training; Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016
• Edited by Friday, November 10, 2017 9:56 AM
Monday, November 22, 2010 7:04 PM
• MY MISTAKE: I need the solution as scalar-valued UDF. I would appreciate if you update your post. Thanks.

Uri's UDF tested 50 msec on Production.Product table (504) rows:

```DECLARE @StartTime datetime

DBCC DROPCLEANBUFFERS

SET @StartTime = GETDATE()

SELECT ExecutionTimeInMS = DATEDIFF(millisecond, @StartTime, getdate())

-- Uri 50 msec```

Kalman Toth, SQL Server & Business Intelligence Training; Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016
• Edited by Friday, November 10, 2017 9:56 AM
Tuesday, November 23, 2010 4:11 PM
• Have you tested George Mastros solution? I believe Brad Schulz also has a version in his

Handy String Functions
Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog
Tuesday, November 23, 2010 4:12 PM
• Have you tested George Mastros solution?

Lightning fast! 6 msec.

```DECLARE @StartTime datetime

DBCC DROPCLEANBUFFERS

SET @StartTime = GETDATE()

SELECT ExecutionTimeInMS = DATEDIFF(millisecond, @StartTime, getdate())

-- Mastros 6 msec
```

Kalman Toth, SQL Server & Business Intelligence Training; Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016
• Edited by Friday, November 10, 2017 9:56 AM
Tuesday, November 23, 2010 4:20 PM
• ```--Date Created: Nov-23-2010
--Author: Pratyaharam
--Purpose : To convert each word of a sentence to proper case
BEGIN
DECLARE @str VARCHAR(8000)
SELECT @str='any senteNce herE with mixed cAsEs'
DECLARE @arr table ( ident int IDENTITY,c CHAR(1))
WHILE LEN(@str) > 0
BEGIN
INSERT INTO @arr
SELECT LEFT(@str,1)
SET @str=CASE WHEN Left(@str,1) =' '
THEN UPPER(SUBSTRING(@str,2,LEN(@str)-1) )
ELSE LOWER(SUBSTRING(@str,2,LEN(@str)-1) ) END;
END
UPDATE @arr set c=UPPER(c) where ident=1
SET @str=''
SELECT @str=@str+c FROM @arr
SELECT @str
END```
Tuesday, November 23, 2010 6:23 PM
• ```--Limitation : No duplicate words in the sentence allowed
--Name: Pratyaharam
--Date: 11-23-2010
--Purpose: to Convert a senternce to proper case ( Limitation : No duplicate words)
IF EXISTS (SELECT * FROM sysobjects WHERE xtype='FN' AND name='MakeProperCase')
BEGIN
DROP FUNCTION dbo.MakeProperCase
END
GO
CREATE FUNCTION dbo.MakeProperCase(@STR VARCHAR(8000))
RETURNS VARCHAR(8000) AS
BEGIN
DECLARE @POS int,@w VARCHAR(100),@result VARCHAR(8000)
SET @result=''
WHILE LEN(@str)>0
BEGIN
SET @pos = charindex(' ',REVERSE(@str),1)
IF @POS = 0 break
SELECT @w=ltrim(rtrim(reverse(LEFT(reverse(@str),@POS))))
SELECT @w=LEFT(upper(@w),1)+SUBSTRING(LOWER(@w),2,LEN(@w))
select @result = @w +' '+ @result
select @str=ltrim(rtrim(reverse(REPLACE(reverse(@str),reverse(@w),''))))
END

RETURN LEFT(upper(@str),1)+SUBSTRING(LOWER(@str),2,LEN(@str)) +' '+ @result
END --function
GO
SELECT dbo.MakeProperCase('any sentEnce wiTh woRds will be turNed to proper case')
SELECT dbo.MakeProperCase('but no duplicates allowed in this version')

```
Tuesday, November 23, 2010 7:39 PM
• What is the best way to get Philadelphia and New York City in the following sample:

```DECLARE @Word nvarchar(32) = 'philadeLphia'

DECLARE @Phrase nvarchar(32) = 'new yorK city'

```
Thanks.
Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM

Also, am I allowed to do this in Oracle, where scalar functions might work better than in SQL Server?

Tuesday, November 23, 2010 7:42 PM
• Have you tested George Mastros solution?

Lightning fast! 6 msec.

```DECLARE @StartTime datetime

DBCC DROPCLEANBUFFERS

SET @StartTime = GETDATE()

SELECT ExecutionTimeInMS = DATEDIFF(millisecond, @StartTime, getdate())

-- Mastros 6 msec

```

Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM

doesnt that table you are testing with have less than 1000 rows in it?

```CREATE FUNCTION dbo.fn_capitalize
(
@str AS nvarchar(4000)
)
RETURNS nvarchar(4000)
AS
BEGIN
RETURN
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE
(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE
(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE
(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE
(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE
(REPLACE(STUFF(@str,1,1,UPPER(SUBSTRING(@str,1,1)))
,' a',' A'),' b',' B'),' c',' C')
,' d',' D'),' e',' E'),' f',' F'),' g',' G')
,' h',' H'),' i',' I'),' j',' J'),' k',' K')
,' l',' L'),' m',' M'),' n',' N'),' o',' O')
,' p',' P'),' q',' Q'),' r',' R'),' s',' S')
,' t',' T'),' u',' U'),' v',' V'),' w',' W')
,' x',' X'),' y',' Y'),' z',' Z')
END
```

Jon
Tuesday, November 23, 2010 8:02 PM

Also, am I allowed to do this in Oracle, where scalar functions might work better than in SQL Server?

Best: best performing.  Sorry, SS only.
Kalman Toth, SQL Server & Business Intelligence Training; Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016
• Edited by Friday, November 10, 2017 9:56 AM
Tuesday, November 23, 2010 8:12 PM
• Thanks Jon - results (504 rows):

```DECLARE @StartTime datetime

DBCC DROPCLEANBUFFERS

SET @StartTime = GETDATE()

SELECT ExecutionTimeInMS = DATEDIFF(millisecond, @StartTime, getdate())

-- Gurgul 70 msec
```

Kalman Toth, SQL Server & Business Intelligence Training; Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016
• Edited by Friday, November 10, 2017 9:57 AM
Tuesday, November 23, 2010 8:15 PM
• ```--Limitation : No duplicate words in the sentence allowed

```
Sorry, that is disqualification. I have no control over that.
Kalman Toth, SQL Server & Business Intelligence Training; Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016
• Edited by Friday, November 10, 2017 9:57 AM
Tuesday, November 23, 2010 8:17 PM
• note that the simple approach i posted is going to fall behind quickly with longer strings and multiple spaces.

Jon
Tuesday, November 23, 2010 8:25 PM

Also, am I allowed to do this in Oracle, where scalar functions might work better than in SQL Server?

Best: best performing.  Sorry, SS only.
Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM

Really, what I was getting at was some of the anomolies noted in George's blog. Specifically, names such as:

• McDonald
• O'Hanion
• DeZego
• last names such as Bush-Clinton

I assume I don't have to worry about lower casing words such as "of"

I also assume that we ignore lower casing people that purposely start one or more of their names with a lower case letter and they get upper case names; If a persons name is (sic) james Martin he still gets James Martin

Tuesday, November 23, 2010 8:45 PM
• Bush-Clinton? Obama-Bush? james Martin?

Sympathy only, speed rules.

Kalman Toth, SQL Server & Business Intelligence Training; Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016
• Edited by Friday, November 10, 2017 9:59 AM
Tuesday, November 23, 2010 9:18 PM
• Based on George's blog, but I am not sure how well it performs and I cannot tell whether or not it is "right":

```alter function dbo.Proper(@string varchar(8000))
returns varchar(8000)
with returns null on null input
as

begin

declare @loc integer

select
@string = stuff(lower(@string), 1, 1, upper(left(@string,1))),
@loc = patindex('%[-/ ][a-z]%', @string)

if @loc = 0
return @string

-- ;WITH
--  L0 AS (SELECT 1 AS C UNION ALL SELECT 1), --2 rows
--  L1 AS (SELECT 1 AS C FROM L0 AS A, L0 AS B),--4 rows
--  L2 AS (SELECT 1 AS C FROM L1 AS A, L1 AS B),--16 rows
--  L3 AS (SELECT 1 AS C FROM L2 AS A, L2 AS B),--256 rows
--  L4 AS (SELECT 1 AS C FROM L3 AS A, L3 AS B),--65536 rows
-- Numbers AS (SELECT ROW_NUMBER() OVER(ORDER BY C) AS N FROM L4)
select
@string = stuff(@string, @loc, 2, upper(substring(@string, @loc, 2))),
@loc = @loc + patindex('%[-/ ][a-z]%', substring(@string, @loc+1, 8000))
from numbers
where n <= 8000
and n <= len(@string)
- len(replace(replace(replace(@string,' ',''),'-',''),'/',''))
and @loc > 0

return @string

end

go

```
Wednesday, November 24, 2010 2:34 PM
• Thanks Kent. I am afraid quite sluggish.

A very useful segment from your code:

```-- T-SQL Create a number table
;WITH
L0 AS (SELECT 1 AS C UNION ALL SELECT 1), --2 rows
L1 AS (SELECT 1 AS C FROM L0 AS A, L0 AS B),--4 rows
L2 AS (SELECT 1 AS C FROM L1 AS A, L1 AS B),--16 rows
L3 AS (SELECT 1 AS C FROM L2 AS A, L2 AS B),--256 rows
L4 AS (SELECT 1 AS C FROM L3 AS A, L3 AS B),--65536 rows
Numbers AS (SELECT ROW_NUMBER() OVER(ORDER BY C) AS N FROM L4)
SELECT * into Numbers FROM Numbers
-- (65536 row(s) affected)
```

Kalman Toth, SQL Server & Business Intelligence Training; Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016
• Edited by Friday, November 10, 2017 9:57 AM
Wednesday, November 24, 2010 4:53 PM
• And how did my solution fare?
Michael Asher
• Proposed as answer by Wednesday, November 24, 2010 6:45 PM
• Unproposed as answer by Wednesday, November 24, 2010 7:28 PM
Wednesday, November 24, 2010 5:52 PM
• ```--Function to convert a string to proper case
--Date: 24-Nov-2010
--Limitation: 32 characters long string```
```-- This version is using recursion so has limitations due to limited nesting
if exists (select * from information_schema.ROUTINES
where ROUTINE_NAME='PROPERCASE' and ROUTINE_TYPE='FUNCTION')
drop function dbo.propercase
go
create function dbo.propercase
(
@str varchar(8000),
@pos int =1 ,
@up int =1
)
returns varchar(8000) as
begin
declare @w char(1)
select @w=substring(@str,@pos,1)
select @w = case when @up = 1 then upper(@w)
when @up = 0 then lower(@w)
end;
return
case
when @pos = len(@str) then @w
when @pos < len(@str) and @w<>' ' then @w + dbo.propercase(@str,@pos+1,0)
when @pos < len(@str) and @w=' ' then @w + dbo.propercase(@str,@pos+1,1)
else ''
end;

end
go

select dbo.propercase('nEw yOrK cItY',default,default);

```
• Edited by Friday, November 26, 2010 6:19 PM
Wednesday, November 24, 2010 6:49 PM
• And how did my solution fare?

If your solution is a script, can you change it into scalar-valued UDF? Thanks.

Kalman Toth, SQL Server & Business Intelligence Training;Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016
• Edited by Friday, November 10, 2017 9:57 AM
Wednesday, November 24, 2010 7:21 PM
• Result for pratyaharam  66 msec.

```DECLARE @StartTime datetime

DBCC DROPCLEANBUFFERS

SET @StartTime = GETDATE()

SELECT ExecutionTimeInMS = DATEDIFF(millisecond, @StartTime, getdate())

-- pratyaharam 66 msec
```

Kalman Toth, SQL Server & Business Intelligence Training; Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016
• Edited by Friday, November 10, 2017 9:58 AM
Wednesday, November 24, 2010 7:27 PM
• In UDF form:

```CREATE FUNCTION dbo.fn_Capitalize01
(
@str Varchar(100)
) RETURNS Varchar(100)
AS
BEGIN
DECLARE @i INTEGER
SET @i=1
WHILE @i <= LEN(@str) BEGIN
SET @str = STUFF(@str,@i,1,CASE WHEN SUBSTRING(@str,@i-1,1)<>' ' THEN LOWER(SUBSTRING(@str,@i,1)) ELSE UPPER(SUBSTRING(@str,@i,1)) END)
SET @i = @i + 1
END
RETURN @str
END
GO

CREATE FUNCTION dbo.fn_Capitalize02
(
@str Varchar(100)
) RETURNS Varchar(100)
AS
BEGIN
DECLARE @res Varchar(100)
DECLARE @i INTEGER
DECLARE @c Char
DECLARE @cap Bit
SET @i=1
SET @cap=1
SET @res=''

WHILE @i <= LEN(@str) BEGIN
SET @c = LOWER(SUBSTRING(@str,@i,1))
IF @cap=1
SET @c = UPPER(@c)
IF @c = ' '
SET @cap=1
ELSE
SET @cap=0
SET @res = @res + @c
SET @i = @i + 1
END
RETURN @res
END
GO

```

Michael Asher
• Marked as answer by Friday, December 3, 2010 12:08 AM
Wednesday, November 24, 2010 9:58 PM
• Thanks Michael. 15 & 20 msec.

I retested Mastros, it is 6 msec.

```DECLARE @StartTime datetime

DBCC DROPCLEANBUFFERS

SET @StartTime = GETDATE()

SELECT ExecutionTimeInMS = DATEDIFF(millisecond, @StartTime, getdate())

-- Asher 1 15 msec

DECLARE @StartTime datetime

DBCC DROPCLEANBUFFERS

SET @StartTime = GETDATE()

SELECT ExecutionTimeInMS = DATEDIFF(millisecond, @StartTime, getdate())

-- Asher 2 20 msec

--------------------------------------------------------------------------------

```

Kalman Toth, SQL Server & Business Intelligence Training; Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016
• Edited by Friday, November 10, 2017 9:58 AM
Wednesday, November 24, 2010 11:41 PM
• ```--Date Created: Nov-23-2010
--Author: Pratyaharam
--Purpose : To convert each word of a sentence to proper case

--I posted one version this earlier in this thread but not as a UDF
now converting into UDF and requesting for a retest

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME='fn_capitalize_pratyaharam_revised' and routine_type='FUNCTION')
DROP FUNCTION fn_capitalize_pratyaharam_revised
GO
CREATE FUNCTION fn_capitalize_pratyaharam_revised(@str VARCHAR(MAX))
RETURNS VARCHAR(max)
BEGIN
--DECLARE @str VARCHAR(max)
DECLARE @arr table ( ident int IDENTITY,c CHAR(1))
WHILE LEN(@str) > 0
BEGIN
INSERT INTO @arr
SELECT LEFT(@str,1)
SET @str=CASE WHEN Left(@str,1) =' '
THEN UPPER(SUBSTRING(@str,2,LEN(@str)-1) )
ELSE LOWER(SUBSTRING(@str,2,LEN(@str)-1) ) END;
END
UPDATE @arr set c=UPPER(c) where ident=1
SET @str=''
SELECT @str=@str+c FROM @arr
RETURN @str
END
GO

```
Friday, November 26, 2010 6:17 PM
• This is on the slow side: 350 msec.

```DECLARE @StartTime datetime

DBCC DROPCLEANBUFFERS

SET @StartTime = GETDATE()

SELECT ExecutionTimeInMS = DATEDIFF(millisecond, @StartTime, getdate())
-- pratyaharam_revised: 350 msec

```

Kalman Toth, SQL Server & Business Intelligence Training; Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016
• Edited by Friday, November 10, 2017 9:58 AM
Friday, November 26, 2010 7:34 PM
• Another version

```--using another method to convert to propercase
if object_id('udf_propercase_fast') is not null
drop function udf_propercase_fast
go
create function udf_propercase_fast(@str varchar(8000))
returns varchar (8000) as
begin
declare @upper varchar(8000), @lower varchar(8000), @result varchar(8000), @c1 char(1), @c2 char(1), @index int
select @upper=upper(@str),@lower=lower(@str),@result = '',@index = 1
select @c1= substring(@upper,@index,1),@c2=substring(@lower,@index,1)
select @result = @result + @c1
while (@index<=len(@str) ) begin
while (ascii(@c1) <> ascii(@c2)) begin
select @index = @index + 1
select @c1= substring(@upper,@index,1),@c2=substring(@lower,@index,1)
if ascii(@c1)= ascii(@c2) break
select @result = @result + @c2
end
select @result = @result + @c1
select @index = @index + 1
select @c1= substring(@upper,@index,1),@c2=substring(@lower,@index,1)
select @result = @result + @c1
end
return @result
end
go
select dbo.udf_propercase_fast('any sENtEnce wiLL be cOnVerted tO pRopEr case')

```
Tuesday, November 30, 2010 5:46 PM
• This one clocks in 80 msec. Also notice: "Women'S Mountain Shorts,  M"

```DECLARE @StartTime datetime

DBCC DROPCLEANBUFFERS

SET @StartTime = GETDATE()

SELECT ExecutionTimeInMS = DATEDIFF(millisecond, @StartTime, getdate())
-- pratyaharam_fast: 80 msec

```

Kalman Toth, SQL Server & Business Intelligence Training; Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016
• Edited by Friday, November 10, 2017 9:58 AM
Tuesday, November 30, 2010 6:15 PM
• ```create function dbo.how_fast_is_this_one(@str varchar(max))
returns varchar(max) as
begin
declare @rlen int,@i int
select @rlen=len(@str),@i=1
while (@i <= @rlen)	select @str=substring(@str,2,@rlen-1)+ case when @i=1 or right(@str,1)=' ' then upper(left(@str,1)) else lower(left(@str,1)) end,@i=@i+1
return(@str)
end
go
select dbo.how_fast_is_this_one('hEllo hOw fast is this vErsIon of cAPItALIzATION')

```
Thursday, December 2, 2010 5:33 PM
• This one is 90msec.

```DECLARE @StartTime datetime
DBCC DROPCLEANBUFFERS
SET @StartTime = GETDATE()

SELECT ExecutionTimeInMS = DATEDIFF(millisecond, @StartTime, getdate())
-- dbo.fn_capitalize_pratyaharam_how_fast_is_this_one 90 msec
```

Kalman Toth, SQL Server & Business Intelligence Training; Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016
• Edited by Friday, November 10, 2017 9:59 AM
Thursday, December 2, 2010 8:32 PM
• Have you tested George Mastros solution? I believe Brad Schulz also has a version in his

Handy String Functions

That clocks in 90msec.

```DECLARE @StartTime datetime
DBCC DROPCLEANBUFFERS
SET @StartTime = GETDATE()