65.9K
CodeProject is changing. Read more.
Home

Capitalize the first character in SQL Server

starIcon
emptyStarIcon
starIcon
emptyStarIconemptyStarIconemptyStarIcon

1.25/5 (10 votes)

May 15, 2006

viewsIcon

119856

downloadIcon

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