CODEWITHSUNDEEP
sqlsql-serverstringperformancereplace
Matthew Baker
Matthew Baker

Reputation: 2729

SQL Server Multiple Replacements

Using SQL Server 2008 R2 - 2016. I have inherited a function we use to make friendly urls - i.e. multiple character replacements. I've just opened it and the code seems "less than optimal".

It works, and everything in here has a good reason to be there, although some of the replacements look a bit odd. I'm wondering if there is a more efficient option for implementing the repeated replacements.

I know you can nest them one inside another, but with this many replacements that is going to be as ugly as sin. I could push them into a table and loop through that but I can just imagine the efficiency hit we'd take. Does anyone have a better way of doing these replacements? I look at the code and I'm convinced there has to be a better option, but I don't know what.

Any options gratefully received.

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')

This is primarily used as part of the import processes we roll out to our customers and as such it's implementation is RBAR which is less than ideal. But the function is used multiple times against multiple tables so I understand why it was developed like this originally., As the data sets have grown, this is becoming more of an issue.

Upvotes: 3

Views: 101

Answers (1)

John Cappelletti
John Cappelletti

Reputation: 81990

Perhaps something like this would be a little more performant and easier to maintain.

By using a table variable, the sequence of replacements seems to keep correct. I've yet to see an exception/failure.

If you use a table rather than a table variable, be sure to include a sequence number and order by.

Example

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

Returns

A-B-C-Tyres

Upvotes: 3

Related Questions