Question Re: Proper Case Function in Technology Q&A

Proper Case Function

JWise93 — Wed, 11 Apr 2018 10:02:10 GMT

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;

Re: Proper Case Function

Chris26 — Wed, 11 Apr 2018 10:10:11 GMT

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;

Re: Proper Case Function

JWise93 — Wed, 11 Apr 2018 10:14:36 GMT

That did it, thanks a ton!

Re: Proper Case Function

thomas_duemesnil — Thu, 12 Apr 2018 06:31:25 GMT

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