SQL Proper Casing
A T-SQL user defined function that converts text to proper case, capitalising the first letter of every word.
1 min read#SQL
SQL Server has UPPER and LOWER, but nothing built in for proper case. I needed it for a table column filled by an SSIS package from a third party database, where the names arrived in mixed case.
The function below lower-cases the text, then walks through it word by word and upper-cases the first letter of each.
sql
CREATE FUNCTION ProperCase(@inpStr nvarchar(8000))
RETURNS nvarchar(8000) AS
BEGIN
DECLARE @Result nvarchar(8000)
SET @inpStr = LOWER(@inpStr) + ' '
SET @Result = ''
WHILE 1=1
BEGIN
IF PATINDEX('% %',@inpStr) = 0 BREAK
SET @Result = @Result + UPPER(Left(@inpStr,1)) +
SubString(@inpStr,2,CharIndex(' ',@inpStr)-1)
SET @inpStr = SubString(@inpStr, CharIndex(' ',@inpStr)+1,Len(@inpStr))
END
SET @Result = Left(@Result,Len(@Result))
RETURN @Result
END
Example
sql
SELECT dbo.ProperCase('jOHN sMITH')
-- John Smith