UDTF to return a capitalize first letter in each word of a string

capitalize.sql

-- UDTF to return a capitalize first letter in each word of a string

-- Simply paste this gist into ACS SQL and run it to create the UDTF.

-- Note: I am using library QUSRSYS. I suggest you put it into your own tool library

-- It is a cool example how far you can go with SQL: Have fun - 
-- (C) Niels Liisberg 2023

-- This gist is distributed on an "as is" basis, without warranties 
-- or conditions of any kind, either express or implied.
----------------------------------------------------------------------------------------------
create or replace function qusrsys.capitalize (
   name varchar(256)
)  
returns varchar(256)
 no external action 
set option output=*print, commit=*none, dbgview = *list
begin
    declare temp varchar(256); 
    declare outString varchar(256); 
    declare i int;
    declare upperNext int;
    declare c char(1); 
    
    set temp = lower(name);
    set i = 1;
    set upperNext = 1;
    set outString = '';
    while i <=  length(temp) do 
       set c = substr(temp , i ,1);
       if c = ' ' then 
           set upperNext = 1;
       elseif upperNext = 1 then  
           set c  = upper(c);
           set upperNext = 0;
       end if;
       set outString = outString || c;
       set i = i +1;
    end while;   
    return outString;
end;

-- usecase 
values qusrsys.capitalize('JOHN A JOHNSON');
values qusrsys.capitalize('john a johnson');
values qusrsys.capitalize('');