Sql scrub data

 

Scenario:

Scrub data through SQL

Solution:

1
2
3
4
5
6
7
8
9
DECLARE @data NVARCHAR(MAX) = 'This check #1 was for $200.', @i INT, @Pattern NVARCHAR(20) = '.$#', @newData NVARCHAR(MAX)

WHILE PATINDEX('%[^' + @Pattern + ']%', @data) <> 0
  BEGIN
      SET @newData = CONCAT(@newData, SUBSTRING(@data,  PATINDEX('%[^' + @Pattern + ']%', @data), 1))
      SET @data = STUFF(@data,  PATINDEX('%[^' + @Pattern + ']%', @data), 1, '')
  END

PRINT @newData