Remove non alpha chars from a column

Question

Remove non alpha chars from a column

vignesh.ms

Hall of Fame

Points: 3456
More actions
March 1, 2014 at 5:07 am

#282962

Hi there,
How to remove non alpha chars from a column ?
I googled the following code
Create Function [dbo].[RemoveNonAlphaCharacters](@Temp VarChar(1000))
Returns VarChar(1000)
AS
Begin
Declare @KeepValues as varchar(50)
Set @KeepValues = '%[^a-z]%'
While PatIndex(@KeepValues, @Temp) > 0
Set @Temp = Stuff(@Temp, PatIndex(@KeepValues, @Temp), 1, '')
Return @Temp
End
is there any other possible way ?
Thanks in advance

Viewing 15 posts - 1 through 15 (of 27 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 1

Yes, there are many other possible ways that use things like Tally Tables, cascading CTEs, etc, but... none of them are faster than what you have. This is one of those rare places where procedural code beats set-based code. I've been trying to beat it since 2007 using set based code and the best I've been able to do is tie it. Oh sure, there's an optimization using nested REPLACEs if you only have a small handful of characters to replace but I don't believe that anyone can beat this particular method using set-based code.

The only function that I've found to be a little faster is an optimization of what you have. The optimization makes it so that PATINDEX is only used once in the loop. Like this...

CREATE FUNCTION dbo.CleanString

/*******************************************************************************

Purpose:

Given a string and a pattern of characters to remove, remove the patterned

characters from the string.

Usage:

--===== Basic Syntax Example

SELECT CleanedString = dbo.CleanString(@pSomeString,@pPattern)

;

--===== Remove all but Alpha characters

SELECT CleanedString = dbo.CleanString(st.SomeString,'%[^A-Za-z]%');

FROM dbo.SomeTable st

;

--===== Remove all but Numeric digits

SELECT CleanedString = dbo.CleanString(st.SomeString,'%[^0-9]%');

FROM dbo.SomeTable st

;

Programmer Notes:

1. @pPattern is case sensitive.

2. The pattern set of characters must be for just one character.

Revision History:

Rev 00 - Circa 2007 - Author Unknown

- Initial find on the web

Rev 01 - 29 Mar 2007 - Jeff Moden

- Optimize to remove one instance of PATINDEX from the loop.

- Add code to use the pattern as a parameter.

Rev 02 - 26 May 2013 - Jeff Moden

- Add case sensitivity

*******************************************************************************/

(@pString VARCHAR(8000),@pPattern VARCHAR(100))

RETURNS VARCHAR(8000) AS

BEGIN

DECLARE @Pos SMALLINT;

SELECT @Pos = PATINDEX(@pPattern,@pString COLLATE Latin1_General_BIN);

WHILE @Pos > 0

SELECT @pString = STUFF(@pString,@Pos,1,''),

@Pos = PATINDEX(@pPattern,@pString COLLATE Latin1_General_BIN);

RETURN @pString;

END

;

GO

Shifting gears a bit, if anyone would like to "race" their function against this one, I'll be happy to setup a test harness. Like I said, I've only been able to tie this one with set-based technology and I'd love to find a faster function because, as you know, I hate it when I have to resort to a WHILE loop and a Scalar Function. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Dwain Camps SSC Guru Points: 86908 More actions · Answer 2

Strange. I would have thought something like this would be faster:

CREATE FUNCTION [dbo].[RemoveUnmatchedPatterns]

(

@STR VARCHAR(8000)

,@Pattern VARCHAR(100)

)

RETURNS TABLE WITH SCHEMABINDING AS

RETURN

WITH Tally (n) AS

(

SELECT TOP (LEN(@Str)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) a(n)

CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) b(n)

CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) c(n)

CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0)) d(n)

),

SplitString AS

(

SELECT code=@Str, n, s=SUBSTRING(@Str, n, 1)

FROM Tally

WHERE PATINDEX(@Pattern, SUBSTRING(@Str COLLATE Latin1_General_BIN, n, 1)) <> 0

)

SELECT ReducedString=

(

SELECT s + ''

FROM SplitString b

ORDER BY n

FOR XML PATH('')

);

GO

WITH Tally (n) AS

(

SELECT TOP 10000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

FROM sys.all_columns a CROSS JOIN sys.all_columns b

)

SELECT MyStr=REPLICATE(SUBSTRING('abcdefghijklmnopqrstuvwxyz', 1+ABS(CHECKSUM(NEWID()))%26, 1), 1+ABS(CHECKSUM(NEWID()))%26)