问题:

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.

Examples:

Input: ‘my dog & me’ return: ‘my dog & me’

Input: ‘my dog and me’ return: ‘my dog and me’

回答1:

Scalar-valued function:

CREATE FUNCTION fReplaceAmpersand (@InputString VARCHAR(255))
RETURNS VARCHAR(1000)
AS  
BEGIN
    RETURN REPLACE(@InputString, '&', '&amp;')
END
GO

Usage:

SELECT dbo.fReplaceAmpersand(MyText)
FROM MyTable

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, '&', '&amp;') 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.