Function error for Upper Case and Lower Case-CodePudding

I have a function in SQL Server (version 2008) which converts a column data to Proper Case (make first letter of a string to upper case and rest to lower). The function works fine, however, there is a need for me to use upper case for data appearing as 'Rto.' There are several records in my table that has for example: 'Rto Dist Inc-Brantford', 'Rto Dist Inc-Orillia' and so on. Originally, the data was all upper case but since I used the function to convert all upper case to Proper case, 'RTO..' has also been converted to Proper Case. I would like to keep Rto in all upper case like this: RTO.

I have included my function below with the last part converting the Rto to RTO but this is where I am getting error message from SQL Server that says:

Msg 102, Level 15, State 1, Procedure ProperCase, Line 29 Incorrect syntax near '@Ret'.

My function:

`GO
/****** Object:  UserDefinedFunction [dbo].[ProperCase]    Script Date: 2022-11-30 9:38:28 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER function [dbo].[ProperCase](@Text as varchar(8000))
returns varchar(8000)
as
begin
  declare @Reset bit;
  declare @Ret varchar(8000);
  declare @i int;
  declare @c char(1);

  if @Text is null
    return null;

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

  while (@i <= len(@Text))
    select @c = substring(@Text, @i, 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,
      @i = @i   1
      **if LEFT(@Ret, 3) = 'Rto' 
      @Ret = UPPER(@Text);**
  return @Ret
end

The block of code in bold:

`if LEFT(@Ret, 3) = 'Rto' 
@Ret = UPPER(@Text);`

was added by me to convert all data appearing as 'Rto.....' to RTO.....

Any guidance to make this code work properly is greatly appreciated

Thank you

CodePudding user response：

I understand why you are using the while loop. It looks like you're trying to make the first character of each word into upper case. I would just add an IF to change the loop variables before entering the while loop. Something like this:

ALTER function [dbo].[ProperCase](@Text as varchar(8000))
returns varchar(8000)
as
begin
  declare @Reset bit;
  declare @Ret varchar(8000);
  declare @i int;
  declare @c char(1);

  if @Text is null
    return null;

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

  --Special handling for strings starting with "RTO".
  if LEFT(@Text,3) = 'rto'
    select @Reset = 1, @i = 4, @Ret = UPPER(LEFT(@Text,3));

  while (@i <= len(@Text))
    select @c = substring(@Text, @i, 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,
      @i = @i   1
  return @Ret
end