This is the function that I am trying to make below with two parameters and one single output that is the matching words. I am using @searchentry and @bestmatch for my parameters. My questions is where should the parameters go in the function so that I can just call the function when it is created Select dbo.FunMatch('enamel cleaner', 'cleaner') it will excecute the function and return the matching words from the two arguments which would be 1 ?

Create Function dbo.FunMatch(
    @searchentry varchar,
    @bestmatch varchar
    ) 
    Returns INT
    As
    Begin
    Declare @output INT
    Set @output = (select
    @searchentry,
    @bestmatch,
    cast(count(isMatch) as float) as matchingWords
from
(
    select 
        s.value as word_from_search_entry_txt, 
        b.value as word_from_best_match,
        case 
            when s.value = b.value or s.value 's'=b.value or s.value=b.value 's' then 'match' 
            else null 
        end as isMatch,
        t.*
     from (
        SELECT 
            @searchentry,@bestmatch
        FROM #tmp_parts
        
    ) t
    cross apply 
    string_split(@searchentry, ' ') s
    cross apply
    string_split(@bestmatch, ' ') b
) a
group by
    @searchentry,
    @bestmatch)
        
        Return @output

I am writing a function to return the matching words between two strings. example data below

CREATE TABLE #tmp_parts
(
search_entry_txt VARCHAR(30),
best_match VARCHAR(30),
);
INSERT INTO #tmp_parts
VALUES ('rotating conveyors', 'conveyor'),
('rivet tool', 'rivet nut tool'),
('enamel cleaner', 'cleaner'),
('farm ring', 'ring'),
('tire gauge', 'gauge'),
('ice cream','ice cream');

You can see the expected out here which is the matchingWords column

select
    search_entry_txt,
    best_match,
    cast(count(isMatch) as float) as matchingWords
from
(
    select 
        s.value as word_from_search_entry_txt, 
        b.value as word_from_best_match,
        case 
            when s.value = b.value or s.value 's'=b.value or s.value=b.value 's' then 'match' 
            else null 
        end as isMatch,
        t.*
     from (
        SELECT 
            search_entry_txt,best_match
        FROM #tmp_parts
    ) t
    cross apply 
    string_split(search_entry_txt, ' ') s
    cross apply
    string_split(best_match, ' ') b
) a
group by
    search_entry_txt,
    best_match

CodePudding user response：

There are some issues with your function script.

1. The parameters @searchentry, @bestmatch might add type length otherwise that will declare length as 1.
2. you are missing the END on the function end.
3. from your code you don't need to use #tmp_parts temp table, just use parameters @searchentry, @bestmatch.
4. There are some verbosity script you might not need, (group by part, subquery which be able to use aggregate function to instead)

I had rewritten your script, you can try this.

Create Function dbo.FunMatch(
    @searchentry varchar(max),
    @bestmatch varchar(max)
) 
Returns INT
As
Begin
    Declare @output INT
    set @output =(select 
    COUNT(case 
        when s.value = b.value or s.value 's'=b.value or s.value=b.value 's' then 'match' 
        else null 
    end) 
    from 
    string_split(@searchentry, ' ') s
    cross apply
    string_split(@bestmatch, ' ') b)
    Return @output
END

sqlfiddle