我需要帮助创建一个 TSQL 函数 [英] I need help create a TSQL function
问题描述
我需要创建一个函数 (EscapeAmpersand).该函数应采用 1 个 varchar(255) 参数.它应该返回一个 varchar(255) 参数.该函数的目的是获取输入,将‘&’
替换为‘&’
并返回结果.
I need a function created (EscapeAmpersand). The function should take 1 varchar(255) parameter. It should return a varchar(255) parameter. The purpose of the function is to take the input, replace ‘&’
with ‘&’
and return the results.
示例:
输入:'my dog &me'
返回:'我的狗 &我'
输入:‘我的狗和我’
返回:‘我的狗和我’
推荐答案
标量值函数:
CREATE FUNCTION fReplaceAmpersand (@InputString VARCHAR(255))
RETURNS VARCHAR(1000)
AS
BEGIN
RETURN REPLACE(@InputString, '&', '&')
END
GO
用法:
SELECT dbo.fReplaceAmpersand(MyText)
FROM MyTable
从性能的角度来看,如果您的表非常大,您最好使用表值函数和 OUTER APPLY:
From a performance standpoint, if your tables are very large you'll be better off using a table-valued function and OUTER APPLY:
CREATE FUNCTION fReplaceAmpersand (@InputString VARCHAR(255))
RETURNS TABLE
AS
RETURN
SELECT REPLACE(@InputString, '&', '&') AS OutputString
GO
-- ----------------------------------------------------
SELECT a.OutputString
FROM MyTable m
OUTER APPLY dbo.fReplaceAmpersand(m.MyText) a
非过程表值函数的运行速度比标量值函数快几倍.
Non-procedural table-valued functions can operate several times faster than scalar-valued ones.
这篇关于我需要帮助创建一个 TSQL 函数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!