HomeSQL ServerSQL Function to make all TEXT to propercase in SQL Server

SQL Function to make all TEXT to propercase in SQL Server

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

SQL Function to make all TEXT to propercase in SQL Server

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

Leave a Reply

You May Also Like

When dealing with a relational database management system (RDBMS) like SQL Server, compatibility level is an important concept to understand....
In this blog post, let’s learn about the error message “49975 – Unable to load controller client certificate due to...
In this blog post, let’s learn about the error message “49973 – Cannot remove tempdb remote file to local tempdb...