ASA UDF to return results from STRING_SPLIT TVF

New Contributor

I'd like to create a function in ASA to capitalize the first letter of each word.  I've got the logic figured out.  The problem is that I can't use a SELECT in a UDF apparently.  I'm not selecting from a table, just a table value function, like so.

select string_agg(stuff(value,1,1,upper(SUBSTRING(value,1,1))),' ') as INITCAP 
from STRING_SPLIT('this is A WORrd', ' ');

 This function cannot be created, with the following error.

create function rpt.udfInitCap(@string varchar(8000))
returns varchar(8000) 
as
begin
    declare @return varchar(8000);
    select @return = string_agg(stuff(value,1,1,upper(substring(value,1,1))),' ') 
        from STRING_SPLIT(@string, ' ');
    return @return;
end

Error: The SELECT statement is not allowed in user-defined functions.

 

Any ideas on how to work around this?

0 Replies