Suppose we have data in lower & upper letters in any column of table like Name or Place and we need result as a proper case or Title case, then we can create a function to get data of any column in Proper case or title case.
Example:
select dbo.ProperCase('Robert AlBERT EinstEIN')
Result:
Robert Albert Einstein
Function to get Proper Case:
Create function [dbo].[ProperCase](@Text as varchar(max))
returns varchar(max)
as
begin
declare @Reset bit;
declare @Return urn varchar(max);
declare @i int;
declare @c char(1);
if @Text is null
return null;
select @Reset = 1, @i = 1, @Return = '';
while (@i <= len(@Text))
select @c = substring(@Text, @i, 1),
@Return = @Return + case when @Reset = 1 then UPPER(@c) else LOWER(@c) end,
@Reset = case when @c like '[a-zA-Z]' then 0 else 1 end,
@i = @i + 1
return @Return
end
