Date: 6 Jun 2012
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.
DROP FUNCTION [dbo].[udf_RemoveSelectedCharactersFromString]
CREATE FUNCTION [dbo].[udf_RemoveSelectedCharactersFromString]
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
-- Example removes non-alphabetic characters
SELECT dbo.[udf_RemoveSelectedCharactersFromString]('2131231Hello123123 234234World6546', '%[a-zA-Z ]%')