Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles / database / SQL-Server

Capitalize the first character in SQL Server

1.25/5 (13 votes)
15 May 2006 1   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.

SQL
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

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here