我有一个表格,其中包含一列原始电子邮件文本,包括标题和邮件正文。这可能包括也可能不包括来自我的域的网址。如果它确实包含来自我的域的 URL,我想在结果中添加一行并增加该 URL 的出现次数。
例如,结果应如下所示:
Link Count
---- -----
a 19
b 5
c 1
对此有何明智之举?
我在SQLMag(Brian Moran)上发现了一个可能有用的函数:请注意,我还没有测试过该功能,所以你可能想自己做:)
CREATE function WordRepeatedNumTimes
(@SourceString varchar(8000),@TargetWord varchar(8000))
RETURNS int
AS
BEGIN
DECLARE @NumTimesRepeated int
,@CurrentStringPosition int
,@LengthOfString int
,@PatternStartsAtPosition int
,@LengthOfTargetWord int
,@NewSourceString varchar(8000)
SET @LengthOfTargetWord = len(@TargetWord)
SET @LengthOfString = len(@SourceString)
SET @NumTimesRepeated = 0
SET @CurrentStringPosition = 0
SET @PatternStartsAtPosition = 0
SET @NewSourceString = @SourceString
WHILE len(@NewSourceString) >= @LengthOfTargetWord
BEGIN
SET @PatternStartsAtPosition = CHARINDEX
(@TargetWord,@NewSourceString)
IF @PatternStartsAtPosition <> 0
BEGIN
SET @NumTimesRepeated = @NumTimesRepeated + 1
SET @CurrentStringPosition = @CurrentStringPosition + @PatternStartsAtPosition +
@LengthOfTargetWord
SET @NewSourceString = substring(@NewSourceString,
@PatternStartsAtPosition +
@LengthOfTargetWord, @LengthOfString)
END
ELSE
BEGIN
SET @NewSourceString = ''
END
END
RETURN @NumTimesRepeated
END
然后,您可以通过以下方式使用它:
DECLARE @link varchar(max)='http://YourLinkHere.com'
SELECT SUM(dbo.WordRepeatedNumTimes(field, @link))
FROM Table
原始文章可以在这里找到