我有一个相当简单的函数,它接受一个字符串,检查值并替换它们,在所有替换之后返回结果字符串。没有回路什么的。
但是,当我运行它时,它就像被卡在循环中一样,不会返回任何值。
以下是相关功能:
ALTER FUNCTION [dbo].[fn_DeNormalizeCo] (@oldStr varchar(255))
RETURNS varchar(255)
BEGIN
DECLARE
@newStr varchar(255)
SET @newStr = LTRIM(RTRIM(@oldStr))
-- Tab \t
IF (@newStr LIKE '%'+CHAR(9)+'%')
SET @newStr = REPLACE(@newStr, CHAR(9), '')
-- Linefeed \n
IF (@newStr LIKE '%'+CHAR(10)+'%')
SET @newStr = REPLACE(@newStr, CHAR(10), '')
-- Carriage Return \r
IF (@newStr LIKE '%'+CHAR(13)+'%')
SET @newStr = REPLACE(@newStr, CHAR(13), '')
-- °
IF (@newStr LIKE '%'+CHAR(176)+'%')
SET @newStr = REPLACE(@newStr, CHAR(176), '')
IF (@newStr LIKE '%#%')
SET @newStr = REPLACE(@newStr, '#', '')
IF (@newStr LIKE '%:%')
SET @newStr = REPLACE(@newStr, ':', '')
IF (@newStr LIKE '%;%')
SET @newStr = REPLACE(@newStr, ';', '')
IF (@newStr LIKE '%@%')
SET @newStr = REPLACE(@newStr, '@', '')
IF (@newStr LIKE '%[%]%')
SET @newStr = REPLACE(@newStr, '%', '')
IF (@newStr LIKE '%*%')
SET @newStr = REPLACE(@newStr, '*', '')
IF (@newStr LIKE '%&%')
SET @newStr = REPLACE(@newStr, '&', '')
IF (@newStr LIKE '%!%')
SET @newStr = REPLACE(@newStr, '!', '')
IF (@newStr LIKE '%$%')
SET @newStr = REPLACE(@newStr, '$', '')
IF (@newStr LIKE '%"%')
SET @newStr = REPLACE(@newStr, '"', '')
IF (@newStr LIKE '%''%')
SET @newStr = REPLACE(@newStr, '''', '')
IF (@newStr LIKE '%,INC.%')
SET @newStr = REPLACE(@newStr, ',INC.', '')
IF (@newStr LIKE '%,INC%')
SET @newStr = REPLACE(@newStr, ',INC', '')
IF (@newStr LIKE '%, INC.%')
SET @newStr = REPLACE(@newStr, ', INC.', '')
IF (@newStr LIKE '%, INC%')
SET @newStr = REPLACE(@newStr, ', INC', '')
IF (@newStr LIKE '% INC.')
SET @newStr = REPLACE(@newStr, ' INC.', '')
IF (@newStr LIKE '% INC')
SET @newStr = REPLACE(@newStr, ' INC', '')
IF (@newStr LIKE '%,LLC.%')
SET @newStr = REPLACE(@newStr, ',LLC.', '')
IF (@newStr LIKE '%,LLC%')
SET @newStr = REPLACE(@newStr, ',LLC', '')
IF (@newStr LIKE '%, LLC.%')
SET @newStr = REPLACE(@newStr, ', LLC.', '')
IF (@newStr LIKE '%, LLC%')
SET @newStr = REPLACE(@newStr, ', LLC', '')
IF (@newStr LIKE '%LLC.')
SET @newStr = REPLACE(@newStr, ' LLC.', '')
IF (@newStr LIKE '% LLC')
SET @newStr = REPLACE(@newStr, ' LLC', '')
IF (@newStr LIKE '%,CO.%')
SET @newStr = REPLACE(@newStr, ',CO.', '')
IF (@newStr LIKE '%,CO%')
SET @newStr = REPLACE(@newStr, ',CO', '')
IF (@newStr LIKE '%, CO.%')
SET @newStr = REPLACE(@newStr, ', CO.', '')
IF (@newStr LIKE '%, CO%')
SET @newStr = REPLACE(@newStr, ', CO', '')
IF (@newStr LIKE '% CO.%')
SET @newStr = REPLACE(@newStr, ' CO.', '')
IF (@newStr LIKE '% CO%')
SET @newStr = REPLACE(@newStr, ' CO', '')
IF (@newStr LIKE '%,CORP.')
SET @newStr = REPLACE(@newStr, ',CORP.', '')
IF (@newStr LIKE '%,CORP')
SET @newStr = REPLACE(@newStr, ',CORP', '')
IF (@newStr LIKE '%, CORP.')
SET @newStr = REPLACE(@newStr, ', CORP.', '')
IF (@newStr LIKE '%, CORP')
SET @newStr = REPLACE(@newStr, ', CORP', '')
IF (@newStr LIKE '% CORP.')
SET @newStr = REPLACE(@newStr, ' CORP.', '')
IF (@newStr LIKE '% CORP')
SET @newStr = REPLACE(@newStr, ' CORP', '')
IF (@newStr LIKE '%,MFG.')
SET @newStr = REPLACE(@newStr, ',MFG.', '')
IF (@newStr LIKE '%,MFG')
SET @newStr = REPLACE(@newStr, ',MFG', '')
IF (@newStr LIKE '%, MFG.')
SET @newStr = REPLACE(@newStr, ', MFG.', '')
IF (@newStr LIKE '%, MFG')
SET @newStr = REPLACE(@newStr, ', MFG', '')
IF (@newStr LIKE '% MFG.')
SET @newStr = REPLACE(@newStr, ' MFG.', '')
IF (@newStr LIKE '% MFG')
SET @newStr = REPLACE(@newStr, ' MFG', '')
IF (@newStr LIKE '%,INCORPORATED.%')
SET @newStr = REPLACE(@newStr, ',INCORPORATED.', '')
IF (@newStr LIKE '%,INCORPORATED%')
SET @newStr = REPLACE(@newStr, ',INCORPORATED', '')
IF (@newStr LIKE '%, INCORPORATED.%')
SET @newStr = REPLACE(@newStr, ', INCORPORATED.', '')
IF (@newStr LIKE '%, INCORPORATED%')
SET @newStr = REPLACE(@newStr, ', INCORPORATED', '')
IF (@newStr LIKE '% INCORPORATED.')
SET @newStr = REPLACE(@newStr, 'INCORPORATED.', '')
IF (@newStr LIKE '% INCORPORATED')
SET @newStr = REPLACE(@newStr, 'INCORPORATED', '')
RETURN @newStr
END
我试过一个简单的电话: SELECT [ULTRA].[dbo].[fn_DeNormalizeCo]('Noname LLC')
为了排除从另一个数据库调用函数的可能性,我还尝试了使用 SELECT [dbo].[fn_DeNormalizeCo]('Noname LLC')
和被卡住的结果一样。
我在这里真的很茫然,我发誓在我必须擦除和重新加载我的电脑之前,我已经很好地使用了这个功能。
我正在使用:
sql server 2019(v15.0.2070.41)
ssms版本18.5
1条答案
按热度按时间oo7oh9g91#
因为您使用的是sql server 2019,所以可能值得一试
.. 如果数据库兼容级别为150并启用了内联。
标量内联仍然是已知的bug。