Monday, November 4, 2013

SQL - Remove Non-Alpha Characters

This function will remove all non-alphanumeric characters.
USE MIGRATION_DB
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID (N'dbo.RemoveNonAlphaCharacters', N'FN') IS NOT NULL
DROP FUNCTION dbo.RemoveNonAlphaCharacters;
GO
Create Function [dbo].[RemoveNonAlphaCharacters](@Temp VarChar(max))
Returns VarChar(max)
AS
BEGIN

DECLARE @KeepValues as varchar(50) = '%[^a-z]%'
WHILE PatIndex(@KeepValues, @Temp) > 0
SET @Temp = Stuff(@Temp, PatIndex(@KeepValues, @Temp), 1, '')

RETURN @Temp
END
GO

No comments: