El rincón de JMACOE

Aquí esta el código para recortar caracteres no alfanumericos de una cadena. Como puedes ver, este script usa el enfoque de conteo de registros. Aquí esta la forma de usarlo:

Select dbo.fnTrimNonAlphaCharacters('2131231Atif123123 234234Sheikh6546')

Esto devolverá ‘Atif Jeque’, después de recortar los caracteres no alfanumericos.

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
--=============================================
-- Author: Atif Sheikh
-- Create date: 28-05-2010
-- Description: Trim Non Alpha Characters
--=============================================
--Select dbo.fnTrimNonAlphaCharacters('2131231Atif123123 234234Sheikh6546')
CREATE FUNCTION [dbo].[fnTrimNonAlphaCharacters](@pString VARCHAR(MAX) )
RETURNS VARCHAR(MAX) AS
BEGIN
	DECLARE @vRetString VARCHAR(MAX)
	SET @vRetString = '' ;
	WITH wcte AS ( 
		SELECT	 TOP(LEN(@pString)) *
		FROM    (
				 SELECT	row_number() over (ORDER BY a.object_id) N
				 FROM	sys.columns A,
						sys.columns b
				) Main
		 )

     SELECT   @vRetString = @vRetString + SUBSTRING(@pString,N,1)
     FROM     wcte A
     WHERE    N <= LEN(@pString)
     AND	 (
				 Ascii(SUBSTRING(@pString,N,1)) BETWEEN 97 AND 122
              OR Ascii(SUBSTRING(@pString,N,1)) BETWEEN 65 AND  90
              OR Ascii(SUBSTRING(@pString,N,1)) = 32
              )
     ORDER BY N
     RETURN @vRetString
END