Change lower case to upper (title) case using sql query

Question

i want to change case using sql query

e.g if text is : My nAme is iShAn halaRNkar (text is jumbled i.e it may contain Lower case or Upper case anywhere in the senetence)

than i want the output to be : My Name Is Ishan Halarnkar

i have not worked on sql queries much. Kindly help.

Answer 1

Here is another Microsoft SQL function:

 CREATE FUNCTION PROPERCASE(@TEXT AS VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
   DECLARE @RESET BIT;
   DECLARE @STR VARCHAR(MAX);
   DECLARE @I INT;
   DECLARE @C CHAR(1);

   SELECT @RESET = 1, @I=1, @STR = '';

   WHILE (@I <= LEN(@TEXT))
    SELECT @C= SUBSTRING(@TEXT,@I,1),
               @STR = @STR + 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 @STR
END