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;

asked 11 Apr, 10:02

JWise93's gravatar image

JWise93
3115
accept rate: 0%


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;
permanent link

answered 11 Apr, 10:10

Christian%20Hamers's gravatar image

Christian Ha...
31181019
accept rate: 50%

1

That did it, thanks a ton!

(11 Apr, 10:14) JWise93

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
permanent link

answered 12 Apr, 06:31

Thomas%20Duemesnil's gravatar image

Thomas Dueme...
2.6k283763
accept rate: 16%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×29
×15

question asked: 11 Apr, 10:02

question was seen: 228 times

last updated: 12 Apr, 06:31