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