SQL Server 多重替换

Question

SQL Server 多重替换

3

使用 SQL Server 2008 R2 - 2016。我继承了一个用于创建友好 URL 的函数 - 即多字符替换。我刚打开它，发现代码似乎“不理想”。尽管此处的所有内容都是有原因的，但有些替换看起来有点奇怪。我在想，在实现重复替换方面是否有更高效的选项。我知道你可以嵌套它们，但是对于这么多替换来说，那会变得非常丑陋。我可以将它们推入一个表中并通过循环来执行操作，但我可以想象我们会受到的效率损失。有没有人有更好的替换方法？我看着这段代码，确信还有更好的选择，但我不知道是什么。任何建议都将不胜感激。

CREATE FUNCTION dbo.MakeFriendlyURL (@stringToConvert NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @Result NVARCHAR(MAX)

    SELECT @Result = CAST(@stringToConvert AS VARCHAR(MAX)) 

    SELECT @Result = REPLACE(@Result, 'ä', 'ae')
    SELECT @Result = REPLACE(@Result, 'ö', 'oe')
    SELECT @Result = REPLACE(@Result, 'ü', 'ue')
    SELECT @Result = REPLACE(@Result, 'Ä', 'Ae')
    SELECT @Result = REPLACE(@Result, 'Ö', 'Oe')
    SELECT @Result = REPLACE(@Result, 'Ü', 'Ue')
    SELECT @Result = REPLACE(@Result, 'ß', 'ss')
    SELECT @Result = REPLACE(@Result, 'é', 'e')
    SELECT @Result = REPLACE(@Result, 'ê', 'e')
    SELECT @Result = REPLACE(@Result, 'à', 'a')
    SELECT @Result = REPLACE(@Result, 'è', 'e')
    SELECT @Result = REPLACE(@Result, 'i', 'i')
    SELECT @Result = REPLACE(@Result, 'l', 'l')
    SELECT @Result = REPLACE(@Result, 'L', 'l')
    SELECT @Result = REPLACE(@Result, 'd', 'd')
    SELECT @Result = REPLACE(@Result, 'ø', 'o')
    SELECT @Result = REPLACE(@Result, 'Þ', 'th')

    SELECT @Result = REPLACE(@Result, ' ', '-')
    SELECT @Result = REPLACE(@Result, '/', '-')
    SELECT @Result = REPLACE(@Result, '&', '-')
    SELECT @Result = REPLACE(@Result, '%', '-')
    SELECT @Result = REPLACE(@Result, ',', '-')
    SELECT @Result = REPLACE(@Result, ';', '-')
    SELECT @Result = REPLACE(@Result, ':', '-')
    SELECT @Result = REPLACE(@Result, '_', '-')

    SELECT @Result = REPLACE(@Result, '+', '')
    SELECT @Result = REPLACE(@Result, '.', '')
    SELECT @Result = REPLACE(@Result, '""', '')
    SELECT @Result = REPLACE(@Result, '*', '')
    SELECT @Result = REPLACE(@Result, '<', '')
    SELECT @Result = REPLACE(@Result, '>', '')
    SELECT @Result = REPLACE(@Result, '?', '')
    SELECT @Result = REPLACE(@Result, '‘', '')
    SELECT @Result = REPLACE(@Result, '’', '')
    SELECT @Result = REPLACE(@Result, CHAR(39), '')

    WHILE CHARINDEX('--', @Result) > 0
    BEGIN    
        SET @Result = REPLACE(@Result, '--', '-')
    END    

    WHILE CHARINDEX('-', @Result) = 1
    BEGIN    
        SET @Result = RIGHT(@Result, LEN(@Result) - 1)
    END    

    WHILE LEN(@Result) > 0 AND SUBSTRING(@Result, LEN(@Result), 1) = '-'
    BEGIN
        SET @Result = LEFT(@Result, LEN(@Result) - 1)
    END

    RETURN @Result
END
GO

SELECT  dbo.MakeFriendlyURL('A,B & C Tyres')

这主要用作我们向客户推出的导入流程的一部分，因此它的实现方式是RBAR，这不是理想的。但是由于该函数在多个表上被多次使用，因此我理解为什么最初会开发出它。随着数据集的增长，这正在变得越来越成问题。

- Matthew Baker

2

SQL并不适用于字符串操作。这种转换应该在客户端使用像C#这样的语言来执行。使用SQLCLR函数会更快。顺便问一下，你为什么要使用未指定代码页的方式将UNICODE转换为ASCII？也许这段代码是试图修复不适当类型的使用吗？ - Panagiotis Kanavos

1

替换也使用ASCII字面量。当您在具有不同排序规则的服务器上执行此代码时，不能保证ø仍然是ø。 - Panagiotis Kanavos

1

如果这是针对 SQL Server 2017 的话，许多替换可以通过单个 TRANSLATE 函数完成一对一字符替换。 - LukStorms

大家好，感谢你们的评论。之前有一个 SQLCLR 版本，但是在 Azure 上支持它变得困难了。代码页已经在调用此程序的单独导入例程中标准化了。 - Matthew Baker

我还没有太多地使用2017，但会研究一下。在这里无法实现，但是与此同时，有一个重新开发的项目正在进行中，可能会采用这种方法。我得去看看。 - Matthew Baker

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- John Cappelletti · Accepted Answer

也许这样做会更高效且易于维护。

通过使用表变量，替换的顺序似乎保持正确。我尚未见过任何异常/故障。

如果您使用的是表而不是表变量，请确保包括一个顺序号并按顺序排列。

示例

Declare @S nvarchar(max) = 'A,B & C Tyres'

Declare @MapValues table (MapFrom nvarchar(50),MapTo nvarchar(50))
Insert Into @MapValues values 
( 'ä', 'ae'),
( 'ö', 'oe'),
( 'ü', 'ue'),
( 'Ä', 'Ae'),
( 'Ö', 'Oe'),
( 'Ü', 'Ue'),
( 'ß', 'ss'),
( 'é', 'e'),
( 'ê', 'e'),
( 'à', 'a'),
( 'è', 'e'),
( 'i', 'i'),
( 'l', 'l'),
( 'L', 'l'),
( 'd', 'd'),
( 'ø', 'o'),
( 'Þ', 'th'),
( ' ', '-'),
( '/', '-'),
( '&', '-'),
( '%', '-'),
( ',', '-'),
( ';', '-'),
( ':', '-'),
( '_', '-'),
( '+', ''),
( '.', ''),
( '""', ''),
( '*', ''),
( '<', ''),
( '>', ''),
( '?', ''),
( '‘', ''),
( '’', ''),
( CHAR(39), ''),
-- Cleanup
( '-----', '-'),
( '----', '-'),
( '---', '-'),
( '--', '-'),
( '     ', '-'),
( '    ', '-'),
( '   ', '-'),
( '  ', '-')


Select @S=Replace(@S,MapFrom,MapTo)
 From  @MapValues

Select @S

返回值

A-B-C-Tyres