Recortar caracteres no alfanuméricos de una cadena
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:
[sql]
Select dbo.fnTrimNonAlphaCharacters(‘2131231Atif123123 234234Sheikh6546’)
[/sql]
Esto devolverá ‘Atif Jeque’, después de recortar los caracteres no alfanumericos.
[sql]
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
[/sql]
