SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Tally Table - String Cleaning

This is something that most people eventually need for reporting purposes.  This function uses a Tally table to 'clean' a string, removing anything you don't specify in the @Rep parameter.  In the case below, I remove everything but letters, numbers, spaces, commas and periods.  This is a slightly modified version of a function I wrote for something else, so the specifics like replacing a lot of the double spaces, and replacing carriage returns and line breaks with spaces were specifically for that.  Remove those pieces of code if you wish, or add those as additional parameters to the function.

Other Methods
I'll mention that I've seen people claim a while loop will beat this method and others that say this method is still faster.  I haven't tested this enough personally to make the claim one way or another, but I prefer this one.  This is also one of the accepted places where a CLR function can be superior to T-SQL, but for people who can't or don't want to use CLR on their servers, this will still work.

Tally Method

/* SELECT dbo.SSC_fn_TallyClean('This will remove all of this---->!)#%*)^+__#@#$+_!)#~!!! <----.','[a-zA-Z0-9. ,]') Cleaned */ CREATE FUNCTION SSC_fn_TallyClean( @A varchar(500), @Rep varchar(100)) RETURNS varchar(500) AS BEGIN DECLARE @B varchar(500) SET @B = '' -- Initialize @B

-- Remove Line Feed / Carriage Returns (The tally code would have removed them, but I wanted to replace them with spaces for readability.)

SET @A = REPLACE(REPLACE(@A,char(10),' '),char(13),' ') SELECT @B = @B + SUBSTRING(@A,N,1) FROM Tally WHERE N <= DATALENGTH(@A) AND SUBSTRING(@A,N,1) LIKE @Rep -- Remove everything but letters, numbers, spaces, period and comma. RETURN REPLACE(REPLACE(@B,' ',' '),' ',' ') -- Removes some double spaces. END


Posted by Stefan Krzywicki on 4 February 2010

"Tally" in this example is just a single column table containing numbers, correct? I'm just getting familiar with the concept of a tally table and want to make sure I understand how it is used in this example. This example requires the tally table to have a column "N" populated with 1 to at least the length of the string?

Posted by jcrawf02 on 4 February 2010

Speaking for Seth here, but yes, Stefan, you are correct. See www.sqlservercentral.com/.../62867 for more info

Posted by Stefan Krzywicki on 4 February 2010

Thanks jcraf02. I'd read that article and wanted to make sure I was applying it correctly in this instance. I appreciate the verification.

Posted by Seth Phelabaum on 21 March 2010

Thanks for filling in Jon, I tend to miss comments after the first few days.  Need to find a way to keep better track.

Posted by Jeff Moden on 31 July 2010

Seth... Rumor has it that you moved a while back.  What's the URL for your local SSUG group now?

Posted by euglenah.l on 20 February 2014

wanna know how to calculate tallies

Leave a Comment

Please register or log in to leave a comment.