Technical Article

Trim Non-Alpha characters from string

,

Here is the code to trim non-alpha charactersfrom the string. As you can see, this script uses tally table approach. Here is the use;

 

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

This will give the result 'Atif Sheikh', after trimming the no-alpha characters.

 

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

Rate

4.45 (11)

You rated this post out of 5. Change rating

Share

Share

Rate

4.45 (11)

You rated this post out of 5. Change rating