Cool Tips‎ > ‎Data Conversion‎ > ‎

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


ċ
Remove non alpha chars from a string.sql
(1k)
Andy Hughes,
Jun 7, 2012, 10:54 AM
Comments