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 @DataENDGOSELECT dbo.CleanToText('info@claytabase.co.uk')

Overview

Returns the following string 'infoclaytabasecouk', removing the "@" and ".".