CODEWITHSUNDEEP
sqlsql-serversql-server-2012
ebelair
ebelair

Reputation: 884

SQL Server replace chars that are not in range

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

Upvotes: 4

Views: 232

Answers (1)

Alex K.
Alex K.

Reputation: 175876

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____

Upvotes: 2

Related Questions