SQL Server replaces characters that are not in range

Question

How can I replace all characters that are not specified in a given range of characters?

DECLARE @s VARCHAR(max)
DECLARE @allowedChars VARCHAR(max)
SET @s = 'Foo@Bar !'
SET @allowedChars = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ$_/+-.0123456789'

SELECT MAGICREPLACE(@s, @allowedChars,'_');
--Should return 'F___B____'

Answer 1

One of the methods:

CREATE FUNCTION [dbo].MAGICREPLACE(@BUFFER VARCHAR(MAX), @PATTERN VARCHAR(128), @REPL VARCHAR(1)) RETURNS VARCHAR(MAX) AS
BEGIN
    DECLARE @POS INT = PATINDEX(@PATTERN, @BUFFER COLLATE SQL_Latin1_General_CP1_CS_AS)
    WHILE @POS > 0 BEGIN
        SET @BUFFER = STUFF(@BUFFER, @POS, 1, @REPL)
        SET @POS = PATINDEX(@PATTERN, @BUFFER COLLATE SQL_Latin1_General_CP1_CS_AS)
    END
    RETURN @BUFFER
END

For

SELECT dbo.MAGICREPLACE('Foo@Bar !', '%[^ABCDEFGHIJKLMNOPQRSTUVWXYZ0-9$_/+-.]%', '_')

> F___B____