alphabet case conversion in sql

Question

I have been trying to create a function in SQL where in if I provide an input "This is a CAt", it should convert the alphabet cases and give an output "tHIS IS A caT"

Below is the function that I have written but it does not seem to work. It looks like the statement SET @Char = SUBSTRING(@InputString,@Index,0) and does not seem to work fine.

Could anyone help with the issue.

CREATE FUNCTION changeCase (@InputString VARCHAR(4000) )
RETURNS VARCHAR(4000)
AS
BEGIN
DECLARE @Index INT
DECLARE @Char CHAR(1)
DECLARE @OutputString VARCHAR(255)
SET @OutputString = @InputString
SET @Index = 1
WHILE @Index <= LEN(@InputString)
BEGIN
SET @Char = SUBSTRING(@OutputString,@Index,0)
IF @Char != LOWER(@Char)
SET @OutputString = STUFF(@OutputString, @Index, 0,LOWER(@Char))
ELSE
SET @OutputString = STUFF(@OutputString, @Index, 0,UPPER(@Char))
SET @Index = @Index + 1
END
RETURN ISNULL(@OutputString,'')
END

Answer 1

A few notes. First of al, as it pointed in the comments the third parameter in the SUBSTRING function should be 1. The same applies to the STUFF function.

Another point is the != operator. Although SQL Server silently accepts it, it is not a standard SQL operator. Use <>

Please use the same size for the @OutputString

And lastly, SQL Server by default makes the case insensitive string comparison. You can fix it with the collates, but I found that the comparison with ASCII works just fine. here is your working code:

declare @InputString VARCHAR(4000) 
DECLARE @Index INT
DECLARE @Char CHAR(1)
DECLARE @OutputString VARCHAR(4000)
set @InputString = 'tHIS IS A caT'
SET @OutputString = @InputString
SET @Index = 1
WHILE @Index <= LEN(@InputString)
BEGIN
 SET @Char = SUBSTRING(@OutputString,@Index,1)
 IF ASCII(@Char) <> ASCII(LOWER(@Char))
  SET @OutputString = STUFF(@OutputString, @Index, 1,LOWER(@Char))
 ELSE
  SET @OutputString = STUFF(@OutputString, @Index, 1,UPPER(@Char))
 SET @Index = @Index + 1
END
print ISNULL(@OutputString,'')