Skip to main content

SQL Server - Function to Convert String to Proper Case


Below is just a quick and simple function to convert a short string to proper case. It's not the most efficient but will do its job for short strings.

The first character of the string is converted to upper case, subsequent characters that are preceded by a space are converted to upper case.

e.g. "this IS A TEST sentence. followed BY ANOTHER."
will be changed to
"This Is A Test Sentence. Followed By Another."

Function:

CREATE FUNCTION [dbo].[fncProperCase](@string VarChar(1000))
RETURNS VarChar(1000)
AS
BEGIN

DECLARE @pos int
DECLARE @len int
DECLARE @char VarChar(1)
DECLARE @temp VarChar(1000)

SET @string = LOWER(LTRIM(RTRIM(@string)))
SET @temp = ''

SET @pos = 1
SET @len = LEN(@string)

WHILE @pos <= @len
BEGIN
SET @char = SUBSTRING(@string, @pos, 1)

IF @pos = 1
BEGIN
SET @temp = @temp + UPPER(@char)
END
ELSE
BEGIN

IF SUBSTRING(@string, @pos - 1, 1) = ' '
BEGIN
SET @temp = @temp + UPPER(@char)
END
ELSE
BEGIN
SET @temp = @temp + @char
END

END

SET @pos = @pos + 1
END

RETURN @temp
END

Example:

SELECT dbo.fncProperCase('this IS A TEST sentence. followed BY ANOTHER.')

Result:

This Is A Test Sentence. Followed By Another.

Labels:

Comments

Post a Comment

Popular posts from this blog

EF CodeFirst Database.SetInitializser Requires Connection to 'master' Database Error

One of the features of EF CodeFirst is the ability to automatically drop and recreate a database if the model changes or if the database does not exist, which is pretty useful when just doing development work. In a web app this is done in the Global.asax file within "protected void Application_Start()" as per this post from Scott Guthrie -  http://weblogs.asp.net/scottgu/archive/2010/07/16/code-first-development-with-entity-framework-4.aspx . An example would be: protected void Application_Start() { Database.SetInitializer<yourdbcontex>(new DropCreateDatabaseIfModelChanges<yourdbcontext>()); AreaRegistration.RegisterAllAreas(); RegisterGlobalFilters(GlobalFilters.Filters); RegisterRoutes(RouteTable.Routes); } However when using the Database.SetInitializer and trying to rebuild the database, the following exception may be encountered, especially if using SQL Server Express: "This operation requires a connection to the 'master'...
4 comments
Read more

VWD Express 2010 Unit Tests with xUnit and MVC Templates

Microsoft's Visual Web Developer 2010 Express (VWD 2010 Express), by default does not come with a built in unit testing solution. However the ability is there to use unit tests with the Express version as can be seen when creating a new MVC project. Tech spec stuff: Microsoft Windows 7 Microsoft Visual Studio 2010 Express, Version 10.0.40219.1 SP1 Microsfot .NET Framework Version 4.0.30319 SP1 Getting started with xUnit xUnit is a "developer testing framework, built to support Test Driven Development (TDD)". There are other unit testing frameworks available (MSTest, nUnit, etc.). There are pro's to each and some better than others but xUnit is pretty straightforward to get up and running with the VWD 2010 Express version. Download and Install xUnit The xUnit project can be found at:  http://xunit.codeplex.com/ . Downloads are available from  http://xunit.codeplex.com/releases/view/62840 . Download the recommended version (currently 1.8 at time of writi...
Post a Comment
Read more

SQL Server - Remove Non-Alphanumeric Characters from String

The following SQL function will remove and strip all non-alphanumeric characters from a string. CREATE FUNCTION [dbo].[fncRemoveNonAlphanumericChars](@Temp VarChar(1000)) RETURNS VarChar(1000) AS BEGIN WHILE PatIndex('%[^A-Za-z0-9]%', @Temp) > 0 SET @Temp = Stuff(@Temp, PatIndex('%[^A-Za-z0-9]%', @Temp), 1, '') RETURN @TEmp END Example: SELECT dbo.fncRemoveNonAlphanumericChars('abc...DEF,,,GHI(((123)))456jklmn') Result: abcDEFGHI123456jklmn
Post a Comment
Read more