Add a condition to a table-valued function

Question

I created this function that splits investornames into first, middle and last. But I want to add a condition which shows in a separate column the number 0 if the middle name has no spaces, and 1 if it has spaces, how can I do this?

This is the function I already created:

ALTER function [dbo].[saveinvestornames]()
returns @investorsname table ( investor_name nvarchar(300),first_name nvarchar(300), middle_name nvarchar(300),last_name nvarchar(300) )
as
begin
    insert into @investorsname
        select 
            investor_name,    
            SUBSTRING(investor_name, CHARINDEX(', ', investor_name) + 2, CASE WHEN CHARINDEX(' ', investor_name, CHARINDEX(', ', investor_name) + 2) = 0 THEN LEN(investor_name) + 1 ELSE CHARINDEX(' ', investor_name, CHARINDEX(', ', investor_name) + 2) END - CHARINDEX(', ', investor_name) - 2)as FirstName,            
            RTRIM(LTRIM(REPLACE(REPLACE(investor_name,SUBSTRING(investor_name , 1, CHARINDEX(' ', investor_name) -1),''),REVERSE( LEFT( REVERSE(investor_name), CHARINDEX(' ', REVERSE(investor_name))-1 ) ),'')))as MiddleName,    
            right(investor_name, CHARINDEX(' ', REVERSE(investor_name))) as LastName
        from investornames;

    return;
end;

Answer 1

Just alter you procedure to:

ALTER function [dbo].[saveinvestornames]()
returns @investorsname table ( investor_name nvarchar(300),first_name nvarchar(300), middle_name nvarchar(300),last_name nvarchar(300), middleNameSpace bit )
as
begin
    insert into @investorsname
        select first_name, middle_name, last_name,
               case when len(middle_name) - len(replace(middle_name, ' ', '')) = 0 then 0 else 1 end
        from (
            select 
                investor_name,    
                SUBSTRING(investor_name, CHARINDEX(', ', investor_name) + 2, CASE WHEN CHARINDEX(' ', investor_name, CHARINDEX(', ', investor_name) + 2) = 0 THEN LEN(investor_name) + 1 ELSE CHARINDEX(' ', investor_name, CHARINDEX(', ', investor_name) + 2) END - CHARINDEX(', ', investor_name) - 2)as FirstName,            
                RTRIM(LTRIM(REPLACE(REPLACE(investor_name,SUBSTRING(investor_name , 1, CHARINDEX(' ', investor_name) -1),''),REVERSE( LEFT( REVERSE(investor_name), CHARINDEX(' ', REVERSE(investor_name))-1 ) ),'')))as MiddleName,    
                right(investor_name, CHARINDEX(' ', REVERSE(investor_name))) as LastName
            from investornames;
        ) a
    return;
end;

It just checks how many spaces there are in middle_name by comparing lengths of this column after replacing space in it.