SQL Server 2008 R2 - 标量 UDF 导致无限循环 [英] SQL Server 2008 R2 - Scalar UDF results in infinite loop

问题描述

以下代码导致无限循环或非常缓慢的执行:

The following code is resulting in an infinite loop or really really slow execution:

CREATE FUNCTION [dbo].[CleanUriPart] 
(
    -- Add the parameters for the function here
    @DirtyUriPart nvarchar(200)
)

RETURNS nvarchar(200)
AS
BEGIN;
    -- Declare the return variable here
    DECLARE @Result nvarchar(200);

DECLARE @i int;

SET @i = 1;

WHILE 1 = 1
BEGIN;
    SET @i = PATINDEX('%[^a-zA-Z0-9.~_-]%', @DirtyUriPart COLLATE Latin1_General_BIN);
    IF @i > 0
        SET @DirtyUriPart = STUFF(@DirtyUriPart, @i, 1, '-');
    ELSE
        BREAK;
END;

-- Add the T-SQL statements to compute the return value here
SELECT @Result = @DirtyUriPart;

-- Return the result of the function
RETURN @Result;

END;

输入/输出应该如下:

• 'abcdef' -> 'abcdef' 工作正常
• 'abc-def' -> 'abc-def' 导致无限循环
• 'abc*def' -> 'abc-def' 导致无限循环
• 等

请帮忙！

推荐答案

SELECT PATINDEX('%[^a-]%', N'aaa-def' COLLATE Latin1_General_BIN),
       PATINDEX('%[^-a]%', N'aaa-def' COLLATE Latin1_General_BIN), 
       PATINDEX('%[^a-]%', 'aaa-def' COLLATE Latin1_General_BIN),
       PATINDEX('%[^-a]%', 'aaa-def' COLLATE Latin1_General_BIN)

退货

----------- ----------- ----------- -----------
1           5           5           5

因此，对于 varchar 数据类型，尾随 - 被视为集合的一部分，而对于 nvarchar 它被忽略(处理作为格式错误的范围，因为 a 也被忽略了?)

So it seems that for varchar datatypes a trailing - is treated as being part of a set whereas for nvarchar it is ignored (treated as a malformed range as a is ignored too?)

LIKE 的 BOL 条目没有明确说明如何在 [] 中使用 - 使其被视为集合的一部分，但确实有示例

The BOL entry for LIKE doesn't explicitly talk about how to use - within [] to get it to be treated as part of a set but does have the example

LIKE '[-acdf]'

匹配 -, a, c, d, or f 所以我假设它需要是集合中的第一项(即 [^a-zA-Z0-9.~_-]需要修改为[^-a-zA-Z0-9.~_]).这也与我上面的测试结果相符.

to match -, a, c, d, or f so I assume that it needs to be the first item in a set (i.e. that [^a-zA-Z0-9.~_-] needs to be altered to [^-a-zA-Z0-9.~_]). That also matches the result of my testing above.

这篇关于SQL Server 2008 R2 - 标量 UDF 导致无限循环的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！