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
Development resources, articles, tutorials, code samples, tools and downloads for ASP.Net, SQL Server, Reporting Services, T-SQL, Windows, AWS, SAP HANA and ABAP


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:

Code

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
Code

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

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

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
Code

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
Code

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



SQL Server

SQL Server 2019 Installation
download SQL Server 2019
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014
download SQL Server 2012
MacOS ve SQL Server 2019


Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.