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
