SQL Server replace chars that are not in range

Question

How i can replace all chars that are not in specified range of chars?

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

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

This operation is called `translate()` (after the Unix command, which has been around a long, long time). There is not a simple way to do this in SQL Server, without writing your own function. — Gordon Linoff, Oct 13 '16 at 12:24
This might help -> http://stackoverflow.com/questions/21378193/regex-pattern-inside-sql-replace-function . As Gordon said, there is no easy way to do this. — sagi, Oct 13 '16 at 12:25

score 2 · Accepted Answer · answered Oct 13 '16 at 12:31

One way:

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____

Excellent! I din't know PATINDEX – ebelair Oct 13 '16 at 12:42 — ebelair, Oct 13 '16 at 12:42

SQL Server replace chars that are not in range

1 Answers1