A SQL Server user-defined function that removes unwanted characters from a string.

use <database_name>;

if exists(select * from sysobjects where name = 'fn_scrub')
    begin
        drop function fn_scrub;
        print 'Success: UDF "fn_scrub" dropped.';
    end;
go

set ansi_nulls, quoted_identifier on;
go

-- ============================================================================
-- Name:         fn_scrub
-- Type:         User Defined Scalar-valued Function
-- Description:  Remove unwanted characters from the specified string.
-- Dependencies: None
--
-- Parameters    nvarchar(max) @string       The string to be scrubed.
--               nvarchar(255) @remove_chars The list of characters to be removed (no separators).
--
-- Returns:      nvarchar(max) The scrubbed string.
--
-- Example:      select dbo.fn_scrub('Peterson #$',' #$');
-- ============================================================================

create function fn_scrub(
    @string nvarchar(max),
    @remove_chars nvarchar(255)
)
returns nvarchar(max)
as
    begin
        declare @RetVal as nvarchar(4000);
        declare @c as nchar(1);
        declare @i bigint;

        set @RetVal = @string;

        -- check if valid parameters passed
        -- if not passthrough input value
        if len(isnull(@string, '')) = 0 or len(isnull(cast(@remove_chars as nvarchar), '')) = 0
            begin
                goto FUNC_EXIT;
            end;

        set @i = 1;
        set @c = '';

        while @i <= len(@remove_chars)
            begin
                set @c = substring(@remove_chars, @i, 1);
                set @RetVal = replace(@RetVal, @c, '');

                set @i = @i + 1;
            end;

        FUNC_EXIT:
        return @RetVal;
    end;
go

if exists(select * from sysobjects where name = 'fn_scrub')
    begin
        print 'Success: UDF "fn_scrub" created.';
    end;
else
    begin
        print 'Failed: Could not create UDF "fn_scrub".';
    end;
go

-- GRANT SELECT TO PUBLIC will be used at Db level