用于将用户输入"sanitize"为可行数据格式的 SQL Server 代码

Question

我在MS SQL Server中创建了一个函数，该函数将允许报表开发人员从用户输入字段中获取数据并将其转换为可行的数据格式。 99%的时间，我使用的代码，下面，有效。 然而，用户总是想办法搞砸他们应该输入的数据（我在一家拥有数千名入门级数据输入员工的非常大的公司工作）。

我主要纠结的是将非常糟糕的用户输入（例如"$1，00，000..411"）转换为可行的十进制格式。 我希望它变成"100000.41"（因为这是我们最接近了解用户键入时脑子里在想什么），但我使用的代码，下面，只会把它变成'100000..411'。

这是我到目前为止编写的代码。 它允许报表开发人员选择他或她想要返回的数据类型类型，还允许传递自定义正则表达式：

CREATE FUNCTION dbo.udf_FieldSanitizer
     (
     @String VARCHAR(250)
     ,@Datatype VARCHAR(250)
     )
RETURNS VARCHAR(250)
AS
BEGIN
     DECLARE @Expression VARCHAR(10)
     SELECT
           @Expression =   CASE
                                WHEN @Datatype = '1' THEN '^0-9-'
                                WHEN @Datatype = '2' THEN '^0-9.-'
                                WHEN @Datatype = '3' THEN '^a-z'
                                WHEN @Datatype = '4' THEN '^a-z '
                                WHEN @Datatype = '5' THEN '^a-z ,'
                                WHEN @Datatype = '6' THEN '^a-z0-9'
                                WHEN @Datatype = '7' THEN '^a-z0-9 '
                                WHEN @Datatype = '8' THEN '^a-z0-9 ,'
                                ELSE @Datatype
                                END
     WHILE PATINDEX('%['+@Expression+']%',@String) > 0
           SET @String = STUFF(@String,PATINDEX('%['+@Expression+']%',@String),1,'')
     RETURN @String
END

Answer 1

好吧

，为了简单地回答您的问题并保持您想要的方式，我建议您在进行 SELECT 之前添加一个检查并替换@String，如下所示：

     DECLARE @Expression VARCHAR(10)
     IF @Datatype = '2'
         AND (SELECT LEN(@String) - LEN(REPLACE(@String,'.',''))) > 1
         SET @String = REPLACE(@String,'..','.')
     SELECT
           @Expression =   CASE

附带说明一下，您的@Datatype被定义为 VARCHAR（150），但您似乎使用的是数字。如果是这样，我会将其更改为 SMALLINT 或 TINYINT，但这是另一篇文章（以及您通常应该如何避免标量函数）。

此外，只要您必须处理自由文本，您就会不断添加这些类型的错误处理子句（用户会找到独特的方法来破坏事物），这就是为什么最好按照 FLICER 的建议将其保留在 UI 级别的原因。