Camel Case...?

[michellepace]

Good morning,

Is there perhaps a function in pervasive where I could convert a string to camel case? That is,

Original: HELLO MARY WHITE
becomes: Hello Mary White

I've searched the Pervasive 13 manual and did not find anything... just trying my luck in case I missed it.

As always, thank you.

 

[mirtheil]

I'm not aware of many Databases that have that kind of function built-in and Actian / Pervasive doesn't have it either. You could always use whatever programming language you're using to build a function to convert it or you could write a DB function to do it.
This might work (I haven't tested it other than to see if it compiled and returned the correct value for one test):

CREATE FUNCTION GetFirstLetterCapital(:str varchar(8000)) RETURNS varchar(8000)
AS
BEGIN
DECLARE :n INT;
SET :n = 1;
DECLARE :pos INT 
SET :pos = 1;
DECLARE :sub VARCHAR(8000);
SET :sub = '';
DECLARE :proper VARCHAR(8000);
SET :proper = '';

if (length(rtrim(ltrim(:str))) > 0) then
begin
    WHILE (:pos > 0) DO
        set :pos = locate(' ',rtrim(ltrim(:str)),:n);
        if (:pos = 0) then
            set :sub = lower(rtrim(ltrim(substring(rtrim(ltrim(:str)),:n))));
        else
        	set :sub = lower(rtrim(ltrim(substring(rtrim(ltrim(:str)),:n, :pos - :n))));
        end if;

        set :proper = concat(concat(:proper, concat(upper(left(:sub,1)),substring(:sub,2))), ' ');
        set :n = :pos + 1;
    END WHILE;
end;
else
  set :proper = '';
end if;

RETURN :proper;
END;

Mirtheil

http://www.mirtheil.com