Proper Case Function

[JWise93]

I'm trying to create a function that produces an output of a string in "Proper Case." I have an attempt put together from other works I've found online, but am not having success. I'm not extremely familiar with SQL Anywhere and was hoping someone could lend a hand.

Here's what I have, but it errors at line 5.

CREATE FUNCTION Proper(in @Text long varchar)
RETURNS long varchar
AS
BEGIN
   declare @Reset   bit;
   declare @Ret   long varchar;
   declare @i   int;
   declare @c   char(1);

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
   return @Ret;
END;

[Chris26 · Accepted solution]

Try this

CREATE FUNCTION Proper(in @Text long varchar)
RETURNS long varchar
BEGIN
   declare @Reset   bit;
   declare @Ret   long varchar;
   declare @i   int;
   declare @c   char(1);

   set @Reset = 1;
   set @i=1;
   set @Ret = '';

while (@i <= len(@Text)) loop
    set @c= substring(@Text,@i,1);
    set @Ret = @Ret + case when @Reset=1 then UPPER(@c) else LOWER(@c) end;
    set @Reset = case when @c like '[a-zA-Z]' then 0 else 1 end;
    set @i = @i +1;
end loop;

   return @Ret;
END;

[JWise93 · Accepted solution]

That did it, thanks a ton!

[thomas_duemesnil]

This would be much faster but does not take into account that you want to keep line feeds or other special characters. Depends on your input string.

BEGIN 
    declare theText long varchar = 'this Is my Text';
    declare theDelimiter char(1) = ' ';

    select list( theProperWords, theDelimiter order by line_num)
    from (
        select string(upper(left(row_value,1)), lower(substr(row_value,2))) theProperWords,
               line_num
        from sa_split_list(theText, theDelimiter )
    ) as wordList;
END