Introduction

We have been getting questions at Transact-SQL forum several times to remove unwanted characters from string. So I have decided to write a DataCleanUp function which removes the unwanted characters from String. DataCleanUp ( String1, InvalidCharacters)

DataCleanUp Function

The syntax for the DataCleanUp function is:

Syntax : DataCleanUp(Data, InvalidCharacters)

Data : Is the string to be clean up from invalid characters and can be of VARCHAR(MAX)

InvalidCharacters : The range of characters which you don't want to be included in your Data and can be of VARCHAR(100) (Ranges could be defined inside [] just like you did for PATINDEX(), also include the '%' before and after your InvalidCharacters Range. For Inverse of your defined range include '^')

SQL Snippet

USE [AdventureWorks]
GO
  
IF OBJECT_ID (N'dbo.DataCleanUp', N'FN') IS NOT NULL
    DROP FUNCTION dbo.DataCleanUp;
GO
CREATE FUNCTION [dbo].[DataCleanUp](@Data VARCHAR(MAX),@InValidData VARCHAR(100))
RETURNS VARCHAR(MAX)
AS
BEGIN
  
DECLARE @CleanUpData VARCHAR(MAX)
SET @CleanUpData = ' ' + @Data + ' '
  
WHILE PATINDEX(@InValidData,@CleanUpData)>0 
BEGIN
SET @CleanUpData = STUFF(@CleanUpData,PATINDEX(@InValidData,@CleanUpData),1,'')
END
  
RETURN LTRIM(RTRIM(@CleanUpData))
  
END

Examples

DataCleanUp('123567576W7XDEWC6A87','%[^1-3A-D]%'); --would return '123DCA'
DataCleanUp('123567576W7XDEWC6A87','%[A-Z]%');  --would return '1235675767687'
DataCleanUp('123567576W7XDEWC6A87','%[0-9]%');  --would return 'WXDEWCA'
DataCleanUp('1275@#7*&*^6^$%W7C6A87','%[^0-9a-z]%'); --would return '127576W7C6A87'
DataCleanUp('1275@#7*&*^6^$%W7C6A87','%[0-9a-z]%'); --would return '@#*&*^^$%'

Microsoft SQL Server Version

This snippet will work on Microsoft SQL Server Version 2005 & above

Other Consideration

I have tested it on a table which has got more than 11 Million rows and it executed fine returning the correct results. Since this is a scalar function you will notice decrease in performance.

Reference Link

This article has originally been posted in TechNet Gallery by myself, see the reference link:

DataCleanUp() Function Implementation in MS SQL Server