Sowbhari - Thursday, December 7, 2017 5:14 AM

Nicely done, but there are couple of drawbacks
1. The function doesn't handle the non-alpha characters in the input string. See what happens if you use ' CONVERT tHis to 1 PROpper cASE' as input.
2. Using a WHILE loop creates a RBAR and can be improved using a Tally table. Refer to the link here for more information.
Below is an improved version of the function utilising ASCII

IF OBJECT_ID('dbo.fnProperCase_V2') IS NOT NULL
 DROP FUNCTION dbo.fnProperCase_V2
GO
CREATE FUNCTION dbo.fnProperCase_V2
(
 @InputString VARCHAR(2000)
)
RETURNS VARCHAR(2000) WITH SCHEMABINDING
AS
BEGIN
 SELECT @InputString = ' ' + LTRIM(RTRIM(LOWER(@InputString))) + ' '
 ;WITH Tens (N) AS
 (
  SELECT N FROM
  (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS A (N)
 ),
 Tally (Num) AS
 (
  SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
  FROM Tens AS A,Tens AS B,Tens AS C,Tens AS D
 )
 SELECT @InputString =
  STUFF(@InputString,Num + 1,1,CHAR(ASCII(SUBSTRING(@InputString,Num + 1,1))-32))
 FROM Tally
 WHERE Num < LEN(@InputString)
  AND SUBSTRING(@InputString,Num,1) = ' '
  AND ASCII(SUBSTRING(@InputString,Num + 1,1)) BETWEEN 97 AND 122
 RETURN(@InputString)
END
GO