New! GraphQL.NET Cookbook by Senthil Kumar Balu

SQL Function to make all TEXT to propercase in SQL Server

There are times when you want to change the tect from the table to a proper case in SQL Server and here’s a small resuable function that lets you do it.

create function ConvertToTitleCase(@Text as varchar(8000))
returns varchar(8000)
as
begin
  declare @Reset bit;
  declare @Ret varchar(8000);
  declare @index int;
  declare @c char(1);

  if @Text is null
    return null;

  select @Reset = 1, @index = 1, @Ret = '';

  while (@index <= len(@Text))
    select @c = substring(@Text, @index, 1),
      @Ret = @Ret + case when @Reset = 1 then UPPER(@c) else LOWER(@c) end,
      @Reset = case when @c like '[a-zA-Z]' then 0 else 1 end,
      @index = @index + 1
  return @Ret
end

You can call the function as shown below.

declare @input varchar(8000) = 'this is a developerpublish.com portal'

select [dbo].[ConvertToTitleCase](@input)

You should see the output as shown below

image

Senthil Kumar B
Senthil Kumar is a former Microsoft MVP (Most Valuable Professional). He is a Co-Author of the book "Windows 10 Development Recipes using JavaScript and CSS" for Apress Publication. He is a technical presenter, blogger, mentor and a Geek.  Senthil is a regular speaker is various local user groups. He has presented at conferences like Great Indian Developer Summit (GIDS) & Microsoft DevCamps. You can reach out to him via his Twitter handle @isenthil.