Returning table with CLR

Question

I want to write an CLR procedure which takes a text and returns a table with all the words in this text. But I can't figure out how to return a table. Could you please tell me it?

    [Microsoft.SqlServer.Server.SqlFunction]
    public static WhatTypeShouldIWriteHere Function1(SqlString str)
    {
        string[] words = Regex.Split(str, @"\W+").Distinct().ToArray();
        //how to return a table with one column of words?
    }

Thank you for your help.

UPDATED: I need to do it for sql-2005

user_267153 · Answer

You can return any list that implements an IEnumerable. Check this out.

user_153865 · Answer

This is a new area of SQL Server, you should consult this article. Which shows the syntax of a table-valued function -- that is what you want to create.

user_9372122 · Answer

    [SqlFunction(DataAccess = DataAccessKind.Read, FillRowMethodName = "FillMatches", TableDefinition = "GroupNumber int, MatchText nvarchar(4000)")]
public static IEnumerable Findall(string Pattern, string Input)
{
    List GroupCollection = new List();

    Regex regex = new Regex(Pattern);
    if (regex.Match(Input).Success)
    {
        int i = 0;
        foreach (Match match in regex.Matches(Input))
        {
            GroupCollection.Add(new RegexMatch(i, match.Groups[0].Value));
            i++;
        }
    }
    return GroupCollection;
}

That was a slight alteration from the code by "Damon Drake"
This one does a findall instead of returning the first value found. so

declare @txt varchar(100) = 'Race Stat 2017-2018 -(FINAL)';
select * from dbo.findall('(\d+)', @txt)

returns

Returning table with CLR

4 Answers