Capitalizing first word in SQL Server
In this article I am going to show you how to use simple function in SQL Server to manipulate with your texts or strings. Imagine you have a bunch of texts in lowercase and they all need to be uppercase or vice versa? You will have to rewrite all that boring thing one by one? Well in SQL Server there is a built-in function that does this conversion for you. Lets say that you want to capitalize every first word. Unfortunately SQL Server has no built-in function for that. You will have to create your own scalar function for that. In this article you will learn how to create that function.
First let me show you how to use the built-in function of SQL Server for uppercase and lowercase
The function name for uppercase is “upper” and for lowercase is “lower”. Now let’s see how this function is used.
select upper('this is the text that needs to be in uppercase')
Run this query above and you will see that all the lowercase will be converted into uppercase.
The query below is going to convert all uppercase letters into lowercase.
select lower('THIS IS THE TEXT THAT NEEDS TO BE IN LOWERCASE')
As I mentioned above for the capitalization of first word you have to create your own function. To create this function type the following query and run. I will name this function as ‘CapFirstWord’
CREATE FUNCTION [dbo].[CapFirstWord] ( @InputStr varchar(4000) )
DECLARE @Index INT
DECLARE @Char CHAR(1)
DECLARE @PrevChar CHAR(1)
DECLARE @OutputStr VARCHAR(255)
SET @OutputStr = LOWER(@InputStr)
SET @Index = 1
WHILE @Index <= LEN(@InputStr)
SET @Char = SUBSTRING(@InputStr, @Index, 1)
SET @PrevChar = CASE WHEN @Index = 1 THEN ' '
ELSE SUBSTRING(@InputStr, @Index - 1, 1)
IF @PrevChar IN (' ', ';', ':', '!', '?', ',', '.', '_', '-', '/', '&', '''', '(')
IF @PrevChar != '''' OR UPPER(@Char) != 'S'
SET @OutputStr = STUFF(@OutputStr, @Index, 1, UPPER(@Char))
SET @Index = @Index + 1
After you create the function above, you can use this in the following way:
select dbo.CapFirstWord('i want to capitalize the first word everywhere')
as [Capitalize First Word]