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


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].


CREATE Function [dbo].[CleanToText]

(@Data VARCHAR(100))

Returns VARCHAR(100)


      DECLARE @Letter INT

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


      WHILE @Letter>0


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

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



      RETURN @Data



SELECT dbo.CleanToText('')

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