Declare @ret_value varchar(max)
            set @ret_value = @p_str1
            set   @ret_value = REPLACE(@ret_value, '.', '')
            set   @ret_value = REPLACE(@ret_value, ',', '') 
            set   @ret_value = REPLACE(@ret_value, '-', '') 
            set   @ret_value = REPLACE(@ret_value, ';', '') 
            set   @ret_value = REPLACE(@ret_value, ':', '')  ')
            RETURN @ret_value  

            Declare @ret_value varchar(max)
            set @ret_value = @p_str1
            set   @ret_value = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@ret_value, '.', ''), ',', ''), '-', '') , ';', ''), ':', '')
            RETURN @ret_value

-- HTML character entity references
-- An authoritative list for HTML 4.0 is at http://www.w3.org/TR/REC-html40/sgml/entities.html
SET @String = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@String, '&',' '), '&',' '),'',' '), ' ',' '),'"',' '), '"',' ')

-- (U.S. centric) Punctuation merely gets in the way of matching; replace with spaces to keep our word separation; we won't do a fully complete replacement, but we'll do what we do relatively quickly.
-- EXCEPT FOR THE # SIGN (which merely gets turned to ' # ', and is used for further address splitting, and is cleaned at the end of that), the bottom three rows of punctuation on the right hand side of a U.S. keyboard, with and without shift by row, then the top row's symbols the same way.
--   Further address splitting: pull the number off the front, then pull the Suite/Apartment/#/Unit/etc. off the end, leaving the middle by itself
SET @String = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@String, '.', ' '), ',', ' '), '/', ' '), '<', ' '), '>', ' '), '?', ' '), ';',' '), '''',' '), ':',' '), '"',' '), '[',' '), ']',' '), '\',' '), '{',' '), '}',' '), '|',' '), '`', ' '), '-', ' '), '=', ' '), '~', ' '), '!', ' '), '@', ' '), '#', ' # '), '$',' '), '%',' '), '^',' '), '&',' '), '*',' '), '(',' '), ')',' '), '_',' '), '+',' ')


-- (U.S. centric) Special characters merely gets in the way of matching, and some may still be in use in old systems and data, particularly mainframes and mainframe derived code.  Here's a list of codes, from more common, to quite rare
-- NOTE: Be careful with UNICODE, though these are all from the first 7 bits of ASCII encoding
SET @String = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@String, CHAR(9),' '), CHAR(11),' '), CHAR(13),' '), CHAR(10),' '), CHAR(0),' '), CHAR(27),' '), CHAR(12),' '), CHAR(1),' '), CHAR(2),' '), CHAR(3),' '), CHAR(4),' '), CHAR(8),' '), CHAR(23),' '), CHAR(24),' '), CHAR(25),' '), CHAR(26),' '), CHAR(28),' '), CHAR(29),' '), CHAR(30),' '), CHAR(31),' ')

-- Get rid of any combination of leading 0's and spaces in a moderately performant way:
SET @StringToStripLeadingZerosSpacesFrom = COALESCE(SUBSTRING(@StringToStripLeadingZerosSpacesFrom,PATINDEX('%[^0 ]%',@StringToStripLeadingZerosSpacesFrom),DATALENGTH(@StringToStripLeadingZerosSpacesFrom)),'')

-- Directions can be changed to standard abbreviations
-- This must be done before punctuations is replaced by spaces.
SET @String = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@String, ' north-east ',' ne '),' northeast ',' ne '), ' north-west ',' nw '),' northwest ',' nw '), ' south-east ',' se '),' southeast ',' se '), ' south-west ',' sw '),' southwest ',' sw '), ' north ',' n '), ' east ',' e '), ' south ',' s '), ' west ',' w ')

-- Dental/Medical centric abbreviation cleaning
-- A select few sourced from http://www.tabers.com/tabersonline/ub/view/Tabers/144251/0/Professional_Designations_and_Titles_in_the_Health_Sciences)
-- Precondition: Punctuation replaced by spaces, and then all consecutive spaces consolidated into single spaces
SET @String = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@String, ' DDS ',' '),' D D S ',' '), ' DMD ',' '),' D M D ',' '), ' PC ',' '),' P C ',' '), ' MS ',' '),' M S ',' '),' PA ',' '),' P A ',' '),' FACD ',' '),' F A C D ',' '),' RDH ',' '),' R D H ',' '),' RDA ',' '),' R D A ',' '),' CDA ',' '),' C D A ',' '),' APC ',' '),' A P C ',' '),' MSD ',' '),' M S D ',' '),' INC ',' '),' I N C ',' '),' MD ',' '),' M D ',' '),' LTD ',' '),' L T D ',' ')


-- Now we get rid of common suffixes
--   This definitely is going to generate false positive matches on parents and children!
--   Use at a fairly fuzzy level of matching, after more precise matches have been handled
SET @String = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@String, ' I ',' '),' II ',' '), ' III ',' '),' IV ',' '), ' V ',' '),' JR ',' '), ' SR ',' ')