SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software SQL Server and T-SQL Development Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, etc.




download SQL Server 2017
download SQL Server 2016
download SQL Server 2014



SQL Capitalize First Letter - SQL Capitalize String

This SQL tutorial shares T-SQL scripts to capitalize string for SQL Server developers.
Sample SQL codes capitalize first letter of words in a sentence or in a string.
Our input is a SQL string that the first letters of each word is converted to upper case letters by SQL codes given in this tutorial.
We know that words in a sentence are distinguished by SPACE character.


Capitalize String in SQL using WHILE Loop

Following SQL code block starts with declaration of input string variable.
The first SELECT command where string functions STUFF() and LOWER() is used, lowers all characters in the given SQL string and replaces the first letter of the string with its upper letter.
We all know that all sentences start with upper case characters. So we implement this requirement in this SQL code line.

The second code block is SQL WHILE loop where I check for space characters and replace the first character following the spece with its upper case version.
Just before WHILE loop and at the end of the inner SQL code block of WHILE loop, I search for the space characters using CHARINDEX function

declare @Word nvarchar(max)
set @Word = 't-sql tutorial - SQL capitalize FIRST letter - sql capitalize string'

declare @Space char(1) = ' '

-- Upper case first letter and all others in lower case letters
select @Word = STUFF(LOWER(@Word), 1, 1, UPPER(LEFT(@Word,1)) )

-- search for space character
declare @i int = CHARINDEX(@Space, @Word, 1)

while @i > 0
begin
 select @i = @i + 1
 -- replace character after space with its upper case letter
 select @Word = STUFF(@Word, @i, 1, UPPER( SUBSTRING(@Word, @i, 1)) )
 -- search for next space character
 select @i = CHARINDEX(@Space, @Word, @i)
end

select @Word

The output of the T-SQL capitalize first letter script is as follows:

SQL code to capitalize string and convert to upper case of first characters after space

SQL capitalize first letters or convert to upper letter after space characters in a string

If this SQL code is enough for you, let's wrap this SQL script into a user-defined function and create your UDF function to capitalize string

create function Capitalize (
 @string nvarchar(max)
) returns nvarchar(max)
as
begin
declare @Space char(1) = ' '

select @string = STUFF(LOWER(@string), 1, 1, UPPER(LEFT(@string,1)) )

declare @i int = CHARINDEX(@Space, @string, 1)

while @i > 0
begin
 select @i = @i + 1
 select @string = STUFF(@string, @i, 1, UPPER( SUBSTRING(@string, @i, 1)) )
 select @i = CHARINDEX(@Space, @string, @i)
end

return @string
end

This UDF user-defined function for capitalize could be used as follows:

select dbo.Capitalize('sql function created by kodyaz.com')

Capitalize First Letters in a String using SQL Split and Concatenate

An other SQL solution for capitalization is to split the variable sentence into words then capitalize first letter. Finally concatenate string using SQL.

In below sample SQL code, I use following SQL SPLIT function first using space character as seperator

declare @Word nvarchar(max)
set @Word = 't-sql tutorial - SQL capitalize FIRST letter - sql capitalize string'

SELECT
 STUFF(
  (
  SELECT
   ' ' + STUFF(val, 1, 1, UPPER(LEFT(val,1)))
  FROM dbo.split(@LOWER(@Word), ' ')
  FOR XML PATH('')
  ), 1, 1, ''
 ) As capitalize_string

That is the output of our second method for capitalizing string variables using SQL split methods and then replacing the first character of each splitted string fragment with its upper case value.

capitalize string using SQL code

Of course, SQL Server database developers can also convert above SQL code block into a user-defined function for converting first letters into capital letters. Here it is:

create function Capitalize (
 @string nvarchar(max)
) returns nvarchar(max)
as
begin
declare @Space char(1) = ' '

SELECT @string =
 STUFF(
  (
  SELECT
   ' ' + STUFF(val, 1, 1, UPPER(LEFT(val,1)))
  FROM dbo.split(LOWER(@string), ' ')
  FOR XML PATH('')
  ), 1, 1, ''
 )

return @string
end

go

I hope these two Capitalize SQL functions help database developers building solutions on SQL Server






Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

MS SQL Server Forums









Copyright © 2004 - 2018 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems