RE: Trimming special characters

SSC Guru

Points: 1004717

December 20, 2012 at 11:38 am

SQLJocky (12/20/2012)
One of our developers created this UDF to trim out special characters...is there a better way to do this?
ALTER FUNCTION [dbo].[UDF_TrimSpecialCharacter]
(
-- Add the parameters for the function here
@String varchar(100)
)
RETURNS VARCHAR(100)
AS
BEGIN
RETURN replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(@String,'-',''),',',''),'_',''),' ',''),'*',''),'.',''),'/',''),'\',''),'(',''),')',''),'#',''),':',''),';',''),'@',''),'~',''),'&','')
END

Only slightly. The nested replaces are incredibly fast and, short of a CLR function, is probably the fastest method. The only other speed enhancement I can see is that it should be converted to an inline table valued function even though it returns a scalar value. To be sure, converting to an inline table valued function that does this can increase the speed of the function by 2 to 7 times. Please see the following article on that...

http://www.sqlservercentral.com/articles/T-SQL/91724/

From a functionality standpoint, I'd make @String a VARCHAR(8000) instead of VARCHAR(100).

--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)