Cleansing text strings in SQL Server keeping letters and numbers in SQL Server

Create a function to cleanse text strings of all characters that are not between 0-9 or A-Z in SQL Server

Overview

Our utilities database is full of neat little functions.

This one is used to cleanse data before we put it into our database, by stripping out all text that isn't between '0' and 'Z', looping though the text string until no items that are not between the values can be found.

The basic function is simply looping through each character of the input value and removing any that do not fall within the Pattern Index range.

If you needed only numbers then [^0-Z] becomes [^0-9]. Alternatively for only text it is [^a-Z].

SQL

CREATE Function [dbo].[CleanToText]

(@Data VARCHAR(100))

Returns VARCHAR(100)

AS BEGIN

      DECLARE @Letter INT

      SET @Letter =PATINDEX('%[^0-Z]%',@Data)

      BEGIN

      WHILE @Letter>0

      BEGIN

      SET @Data =STUFF(@Data,@Letter,1,'')

      SET @Letter =PATINDEX('%[^0-Z]%',@Data)

      END

      END

      RETURN @Data

END

GO

SELECT dbo.CleanToText('gc@gsclayton.net')

Returns the following string 'gcgsclaytonnet', removing the "@" and ".".
Ousia Logo