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 ]%')