Remove Selected Characters from a String

Applicability:

SQL Server 2000: Not Supported **

SQL Server 2005: Tested

SQL Server 2008: Tested

SQL Server 2008R2: Tested

SQL Server 2012: Not Tested

Credits:

Author: Unkown/ChillyDBA

Date: 6 Jun 2012

Description

This function originally arrived as one that removed non-alphabetic characters from a string and contained code to create a numbers table. I realised 2 things:

- The screening of non-alphabetic characters used a REGEX that could easily be passed in as a parameter, thus expanding the flexibility of the function
- The locally produced numbers table and CTE that precluded use on SQL 2000 could be replaced by a function. This numbers table function has several versions, valid across all SQL versions.

The function is now less complex. It utilises the custom numbers table to copy only the characters in the input string to the output string that match the REGEX criteria passed in.

**NOTE: The code as it stands is not supported on SQL Server 2000. However, replacing the udf_NumbersTable function using CTE with the one that is SQL 2000 compliant (available from the same link), should enable it to function.

Code

DROP FUNCTION [dbo].[udf_RemoveSelectedCharactersFromString]

GO

CREATE FUNCTION [dbo].[udf_RemoveSelectedCharactersFromString]

(

@pString VARCHAR(MAX),

@Filter VARCHAR(MAX)

)

RETURNS VARCHAR(MAX)

AS

BEGIN

DECLARE @vRetString VARCHAR(MAX)

SET @vRetString = ''

-- use a list of numbers

-- to copy the inputstring to the output string, one char at a time, but only if it meets the specified criteria

SELECT @vRetString = @vRetString + SUBSTRING(@pString, value,1)

FROM udf_NumbersTable (1, DATALENGTH(@pString))

WHERE SUBSTRING(@pString,value,1) LIKE @Filter --'%[a-zA-Z ]%'

ORDER BY value

RETURN @vRetString

END

GO

-- Example removes non-alphabetic characters

SELECT dbo.[udf_RemoveSelectedCharactersFromString]('2131231Hello123123 234234World6546', '%[a-zA-Z ]%')