Capitalize the first character in SQL Server






1.25/5 (10 votes)
May 15, 2006

119856

253
This user-defined function will allow you to capitalize the first character of any string passed to it.
Introduction
This is just a quick and very simple SQL Script you can use to capitalize the first letter of a string. The best use for it is inline with your SQL Statement much like you use Left, Right, etc.
I needed a way to convert my existing usernames, that were all lower case with each part of the name separated by a period, into usable first and last names. I could have done with in code, but I wanted to bind the results of my query to a drop down list. Therefore, I wrote this script so I could have the formatted text returned, and easily bindable.
The script is designed to be a user-defined function so that it may be called easily from your statment.
Script
CREATE FUNCTION InitCap ( @string varchar(255) ) RETURNS varchar(255) AS BEGIN RETURN upper(left(@string, 1)) + right(@string, len(@string) - 1) END
Examples
The following examples assums a table named tblCustomers with a user_name column. Each username is formatted like erik.bartlow.
SELECT InitCap(Left(user_name, CHARINDEX('.', user_name, 1) - 1)) as First_Name, InitCap(Right(user_name, (Len(id_user) - CHARINDEX('.', user_name, 1)))), as Last_Name, username, FROM tblCustomers ORDER BY Last_Name