SQL Server 中的 REPLACE 函数是否接受来自“string_pattern"参数表的输入? [英] Does REPLACE function in SQL Server accept input from a table for 'string_pattern' parameter?

问题描述

我仍在学习 SQL Server 方面的知识，也许这个问题听起来很幼稚/荒谬.请多多包涵.:)

I'm still learning my ropes with SQL Server and maybe this question sounds very naive/ridiculous. Please bear with me on this. :)

我在 SQL Server 中看到一个函数定义如下:

I saw a function in SQL Server defined as below:

CREATE FUNCTION [dbo].[fn_CleanNumeric]
    (
    @InputString VARCHAR(500)
    )
RETURNS NVARCHAR(500)
as
BEGIN
    SELECT
        @InputString = REPLACE(@InputString, n.Symbol, '')
    FROM 
        NonNumeric n
    RETURN @InputString
END

NonNumeric 是一个表，其中有一列名为 [Symbol]，包含一堆非数字 ASCII 字符.通过查看 的 SQL Server 文档REPLACE，我不认为它 (REPLACE) 接受表的列，也似乎不接受游标.是因为我们在函数上下文中使用了 REPLACE 吗?如果有人能给我指出一个很好的资源来解释它，我将不胜感激.

NonNumeric is a table with a column named, [Symbol], containing a bunch of non-numeric ASCII characters. From looking at SQL Server documentation for REPLACE, I don't think it (REPLACE) accepts a table's column nor does it seem to accept a cursor. Is it because we are using REPLACE in function context? If someone could point me to a good resource that explains it, I'd greatly appreciate that.

先谢谢你！

推荐答案

这是一个名为 古怪的更新

这很慢，在大多数情况下应该避免

This is slow and in most cases something one should avoid

一些例子:

DECLARE @tbl TABLE(SomeInt INT);
INSERT INTO @tbl VALUES (1),(2),(3);

DECLARE @SumOfInts INT=0
SELECT @SumOfInts = @SumOfInts + SomeInt FROM @tbl;
SELECT @SumOfInts; --SELECT SUM(SomeInt) FROM @tbl is clearly better...)

字符串连接

DECLARE @tbl2 TABLE(SomeText VARCHAR(100));
INSERT INTO @tbl2 VALUES ('a'),('b'),('c');

DECLARE @ConcatString VARCHAR(100)='';
SELECT @ConcatString = @ConcatString + ', ' + SomeText FROM @tbl2;
SELECT @ConcatString;

更好的是使用 FOR XML PATH('') 和 STUFF() 的常用方法，这也是一个很糟糕的解决方法.(新版本终于会带内置功能了！)

Better was the usual approach with FOR XML PATH('') and STUFF(), which is a hacky workaround too. (Newer versions will bring a built-in function at last!)

这个古怪更新是我所知道的唯一一种使用表的值逐步替换您可以在表中动态维护的许多值的方法:

This Quirky Update is the only way I know to use a table's values for a step-by-step replacement of many values you can maintain in a table dynamically:

看看这个:

DECLARE @ReplaceValues TABLE(this VARCHAR(100),[by] VARCHAR(100));
INSERT INTO @ReplaceValues VALUES('$',' Dollar'),('€',' Euro'),('abbr.','abbreviations');

DECLARE @SomeText VARCHAR(MAX)=
'This text is about 100 $ or 100 € and shows how to replace abbr.!';

SELECT @SomeText=REPLACE(@SomeText,this,[by]) FROM @ReplaceValues;

SELECT @SomeText;

结果

--This text is about 100  Dollar or 100  Euro and shows how to replace abbreviations!

这篇关于SQL Server 中的 REPLACE 函数是否接受来自“string_pattern"参数表的输入?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！