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 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.
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