C# 使用 CLR 返回表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2243637/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Returning table with CLR
提问by StuffHappens
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?
我想编写一个 CLR 过程，它接受一个文本并返回一个包含文本中所有单词的表格。但我不知道如何返回一张桌子。你能告诉我吗？
[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
更新：我需要为 sql-2005 做这件事
采纳答案by Pedro
回答by Hassan Syed
回答by Damon Drake
Here is a full blown sample. I got tired of searching for this myself and even though this is answered, I thought I would post this just to keep a fresh reference online.
这是一个完整的样本。我已经厌倦了自己搜索这个，即使这得到了回答，我想我会发布这个只是为了在网上保持一个新的参考。
using System;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text.RegularExpressions;
using System.Collections;
using System.Collections.Generic;
public partial class UserDefinedFunctions {
[SqlFunction]
public static SqlBoolean RegexPatternMatch(string Input, string Pattern) {
return Regex.Match(Input, Pattern).Success ? new SqlBoolean(true) : new SqlBoolean(false);
}
[SqlFunction]
public static SqlString RegexGroupValue(string Input, string Pattern, int GroupNumber) {
Match m = Regex.Match(Input, Pattern);
SqlString value = m.Success ? m.Groups[GroupNumber].Value : null;
return value;
}
[SqlFunction(DataAccess = DataAccessKind.Read, FillRowMethodName = "FillMatches", TableDefinition = "GroupNumber int, MatchText nvarchar(4000)")]
public static IEnumerable RegexGroupValues(string Input, string Pattern) {
List<RegexMatch> GroupCollection = new List<RegexMatch>();
Match m = Regex.Match(Input, Pattern);
if (m.Success) {
for (int i = 0; i < m.Groups.Count; i++) {
GroupCollection.Add(new RegexMatch(i, m.Groups[i].Value));
}
}
return GroupCollection;
}
public static void FillMatches(object Group, out SqlInt32 GroupNumber, out SqlString MatchText) {
RegexMatch rm = (RegexMatch)Group;
GroupNumber = rm.GroupNumber;
MatchText = rm.MatchText;
}
private class RegexMatch {
public SqlInt32 GroupNumber { get; set; }
public SqlString MatchText { get; set; }
public RegexMatch(SqlInt32 group, SqlString match) {
this.GroupNumber = group;
this.MatchText = match;
}
}
};
回答by Scourge
[SqlFunction(DataAccess = DataAccessKind.Read, FillRowMethodName = "FillMatches", TableDefinition = "GroupNumber int, MatchText nvarchar(4000)")]
public static IEnumerable Findall(string Pattern, string Input)
{
List<RegexMatch> GroupCollection = new List<RegexMatch>();
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
这是“Damon Drake”对代码的轻微改动。这个代码
执行 findall 而不是返回找到的第一个值。所以
declare @txt varchar(100) = 'Race Stat 2017-2018 -(FINAL)';
select * from dbo.findall('(\d+)', @txt)
returns
返回
