Search This Blog

Wednesday, June 23, 2010

Trim Non-Alpha characters from string

Here is the code to trim non-alpha characters
from the string. This script uses tally table approach.


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

No comments:

Post a Comment