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