And, if anyone cares, here is some U.S. centric address sanitization to add to the clean_string function or set of functions. Given that addresses vary significantly country to country, please review carefully before using in your country.

At the end is also primitive SQL to turn consecutive spaces into single spaces, which can be trivially separated from the PO Box replacements.

Unfortunately, this trivial but tedious method does result in very long SQL, and substantial CPU time for the cleansing function.

-- 238 nestings, ALLEY to LOOP plus " BY PASS " and " BL ", and " BLUFFS ", " BROOKS ", " BURGS ", " CENTERS ", " CLIFFS ", " CIRCLES ", " CORNERS ", " COURTS ", " COVES ", " DRIVES ", " ESTATES ", " EXTENSIONS ", " FALLS ", " FIELDS ", " FLATS ", " FORDS ", " FORGES ", " FORKS ", " GARDENS ", " GLENS ", " GREENS ", " GROVES ", " HARBORS ", " HILLS ", " ISLANDS ", " JUNCTIONS ", " KEYS ", " KNOLLS ", " LAKES ", " LIGHTS ", " LOCKS ", and so on to the singular abbreviation from the official plural abbreviations

-- spaces remain at this point to allow for word separations, created in bulk via Excel and global search and replace, meaningless self-replacements removed.

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(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(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(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(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(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(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(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@String,' ALLEE ',' ALY '),' ALLEY ',' ALY '),' ALLY ',' ALY '),' ANEX ',' ANX '),' ANNEX ',' ANX '),' ANNX ',' ANX '),' ARCADE ',' ARC '),' AV ',' AVE '),' AVEN ',' AVE '),' AVENU ',' AVE '),' AVENUE ',' AVE '),' AVN ',' AVE '),' AVNUE ',' AVE '),' BAYOO ',' BYU '),' BAYOU ',' BYU '),' BEACH ',' BCH '),' BEND ',' BND '),' BLUF ',' BLF '),' BLUFF ',' BLF '),' BLUFFS ',' BLF '),' BLFS ',' BLF '),' BOT ',' BTM '),' BOTTM ',' BTM '),' BOTTOM ',' BTM '),' BL ',' BLVD '),' BOUL ',' BLVD '),' BOULEVARD ',' BLVD '),' BOULV ',' BLVD '),' BRANCH ',' BR '),' BRNCH ',' BR '),' BRDGE ',' BRG '),' BRIDGE ',' BRG '),' BROOK ',' BRK '),' BROOKS ',' BRK '),' BRKS ',' BRK '),' BURG ',' BG '),' BURGS ',' BG '),' BGS ',' BG '),' BYPA ',' BYP '),' BYPAS ',' BYP '),' BYPASS ',' BYP '),' BY PASS ',' BYP '),' BYPS ',' BYP '),' CAMP ',' CP '),' CMP ',' CP '),' CANYN ',' CYN '),' CANYON ',' CYN '),' CNYN ',' CYN '),' CAPE ',' CPE '),' CAUSEWAY ',' CSWY '),' CAUSWAY ',' CSWY ') ,' CEN ',' CTR '),' CENT ',' CTR '),' CENTER ',' CTR '),' CENTR ',' CTR '),' CENTRE ',' CTR '),' CNTER ',' CTR '),' CNTR ',' CTR '),' CENTERS ',' CTR '),' CTRS ',' CTR '),' CIRC ',' CIR '),' CIRCL ',' CIR '),' CIRCLE ',' CIR '),' CRCL ',' CIR '),' CRCLE ',' CIR '),' CIRCLES ',' CIR '),' CIRS ',' CIR '),' CLIFF ',' CLF '),' CLFS ',' CLF '),' CLIFFS ',' CLF '),' CLUB ',' CLB '),' COMMON ',' CMN '),' CORNER ',' COR '),' CORNERS ',' COR '),' CORS ',' COR '),' COURSE ',' CRSE '),' COURT ',' CT '),' CRT ',' CT '),' COURTS ',' CT '),' CTS ',' CT '),' COVE ',' CV '),' CVS ',' CV '),' COVES ',' CV '),' CK ',' CRK '),' CR ',' CRK '),' CREEK ',' CRK '),' CRECENT ',' CRES '),' CRESCENT ',' CRES '),' CRESENT ',' CRES '),' CRSCNT ',' CRES ') ,' CRSENT ',' CRES '),' CRSNT ',' CRES '),' CREST ',' CRST '),' CROSSING ',' XING '),' CRSSING ',' XING '),' CRSSNG ',' XING '),' CROSSROAD ',' XRD '),' CURVE ',' CURV '),' DALE ',' DL '),' DAM ',' DM '),' DIV ',' DV '),' DIVIDE ',' DV '),' DVD ',' DV '),' DRIV ',' DR '),' DRIVE ',' DR '),' DRV ',' DR '),' DRIVES ',' DR '),' DRS ',' DR '),' ESTATE ',' EST '),' ESTATES ',' EST '),' ESTS ',' EST '),' EXP ',' EXPY '),' EXPR ',' EXPY '),' EXPRESS ',' EXPY '),' EXPRESSWAY ',' EXPY '),' EXPW ',' EXPY '),' EXTENSION ',' EXT '),' EXTN ',' EXT '),' EXTNSN ',' EXT '),' EXTENSIONS ',' EXT '),' EXTS ',' EXT '),' FALLS ',' FALL '),' FLS ',' FALL '),' FERRY ',' FRY '),' FRRY ',' FRY '),' FIELD ',' FLD '),' FIELDS ',' FLD '),' FLDS ',' FLD '),' FLAT ',' FLT '),' FLATS ',' FLT '),' FLTS ',' FLT '),' FORD ',' FRD '),' FRDS ',' FRD '),' FORDS ',' FRD '),' FOREST ',' FRST '),' FORESTS ',' FRST '),' FORG ',' FRG '),' FORGE ',' FRG '),' FRGS ',' FRG '),' FORGES ',' FRG '),' FORK ',' FRK '),' FORKS ',' FRK '),' FRKS ',' FRK '),' FORT ',' FT '),' FRT ',' FT '),' FREEWAY ',' FWY '),' FREEWY ',' FWY '),' FRWAY ',' FWY '),' FRWY ',' FWY '),' GARDEN ',' GDN '),' GARDN ',' GDN '),' GRDEN ',' GDN '),' GRDN ',' GDN '),' GARDENS ',' GDN '),' GDNS ',' GDN '),' GRDNS ',' GDN '),' GATEWAY ',' GTWY '),' GATEWY ',' GTWY '),' GATWAY ',' GTWY '),' GTWAY ',' GTWY '),' GLEN ',' GLN '),' GLNS ',' GLN '),' GLENS ',' GLN '),' GREEN ',' GRN '),' GRNS ',' GRN '),' GREENS ',' GRN '),' GROV ',' GRV '),' GROVE ',' GRV '),' GRVS ',' GRV '),' GROVES ',' GRV '),' HARB ',' HBR '),' HARBOR ',' HBR '),' HARBR ',' HBR '),' HBRS ',' HBR ') ,' HRBOR ',' HBR ') ,' HARBORS ',' HBR '),' HAVEN ',' HVN '),' HAVN ',' HVN '),' HEIGHT ',' HTS '),' HEIGHTS ',' HTS '),' HGTS ',' HTS '),' HT ',' HTS '),' HIGHWAY ',' HWY '),' HIGHWY ',' HWY '),' HIWAY ',' HWY '),' HIWY ',' HWY '),' HWAY ',' HWY '),' HILL ',' HL '),' HILLS ',' HL '),' HLS ',' HL '),' HLLW ',' HOLW '),' HOLLOW ',' HOLW '),' HOLLOWS ',' HOLW '),' HOLWS ',' HOLW '),' INLET ',' INLT '),' ISLAND ',' IS '),' ISLND ',' IS '),' ISLANDS ',' IS '),' ISLNDS ',' IS '),' ISS ',' IS '),' ISLES ',' ISLE '),' JCTION ',' JCT '),' JCTN ',' JCT '),' JUNCTION ',' JCT '),' JUNCTN ',' JCT '),' JUNCTON ',' JCT '),' JCTNS ',' JCT '),' JCTS ',' JCT '),' JUNCTIONS ',' JCT '),' KEY ',' KY '),' KEYS ',' KY '),' KYS ',' KY '),' KNL ',' KNL '),' KNOL ',' KNL '),' KNOLL ',' KNL '),' KNLS ',' KNL '),' KNOLLS ',' KNL '),' LAKE ',' LK '),' LK ',' LK '),' LAKES ',' LK '),' LKS ',' LK '),' LAND ',' LAND '),' LANDING ',' LNDG '),' LNDNG ',' LNDG '),' LA ',' LN '),' LANE ',' LN '),' LANES ',' LN '),' LIGHT ',' LGT '),' LIGHTS ',' LGT '),' LGTS ',' LGT '),' LOAF ',' LF '),' LOCK ',' LCK '),' LCKS ',' LCK '),' LOCKS ',' LCK '),' LDGE ',' LDG '),' LODG ',' LDG '),' LODGE ',' LDG '),' LOOPS ',' LOOP ')

-- 201 nestings, MALL to WELLS plus " UNIT " then all secondary designators APARTMENT to UPPER, and " MANORS ", " MEADOWS ", " MILLS ", " MOUNTAINS ", " PINES ", " PLAINS ", " POINTS ", " PORTS ", " RAPIDS ", " RIDGES ", " ROADS ", " SHOALS ", " SHORES ", " SPRINGS ", " SQUARES ", " STREETS ", " UNIONS ", " VALLEYS ", " VIEWS ", " VILLAGES ", " WAYS ", " WELLS ", and so on to the singular abbreviation from the official plural abbreviations

-- spaces remain at this point to allow for word separations, created in bulk via Excel and global search and replace, meaningless self-replacements removed.

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(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(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(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(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(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(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@String,' MANOR ',' MNR '),' MNR ',' MNR '),' MANORS ',' MNR '),' MNRS ',' MNR '),' MEADOW ',' MDW '),' MDWS ',' MDW '),' MEADOWS ',' MDW ') ,' MEDOWS ',' MDW '),' MEWS ',' MEWS '),' MILL ',' ML '),' MILLS ',' ML '),' MLS ',' ML ') ,' MISSION ',' MSN '),' MISSN ',' MSN '),' MSSN ',' MSN '),' MOTORWAY ',' MTWY '),' MNT ',' MT '),' MOUNT ',' MT '),' MNTAIN ',' MTN '),' MNTN ',' MTN '),' MOUNTAIN ',' MTN '),' MOUNTIN ',' MTN '),' MTIN ',' MTN '),' MTNS ',' MTN '),' MNTNS ',' MTN ') ,' MOUNTAINS ',' MTN '),' NECK ',' NCK '),' ORCHARD ',' ORCH '),' ORCHRD ',' ORCH '),' OVL ',' OVAL '),' OVERPASS ',' OPAS '),' PK ',' PARK '),' PRK ',' PARK '),' PARKS ',' PARK '),' PARKWAY ',' PKWY '),' PARKWY ',' PKWY '),' PKWAY ',' PKWY '),' PKY ',' PKWY '),' PARKWAYS ',' PKWY ') ,' PKWYS ',' PKWY '),' PASSAGE ',' PSGE '),' PATHS ',' PATH '),' PIKES ',' PIKE '),' PINE ',' PNE '),' PINES ',' PNE '),' PNES ',' PNE '),' PLACE ',' PL '),' PLAIN ',' PLN '),' PLAINES ',' PLN '),' PLAINS ',' PLN '),' PLNS ',' PLN '),' PLAZA ',' PLZ '),' PLZA ',' PLZ '),' POINT ',' PT '),' POINTS ',' PT '),' PTS ',' PT '),' PORT ',' PRT '),' PORTS ',' PRT '),' PRTS ',' PRT '),' PRAIRIE ',' PR '),' PRARIE ',' PR '),' PRR ',' PR '),' RAD ',' RADL '),' RADIAL ',' RADL '),' RADIEL ',' RADL '),' RANCH ',' RNCH '),' RANCHES ',' RNCH '),' RNCHS ',' RNCH '),' RAPID ',' RPD '),' RAPIDS ',' RPD '),' RPDS ',' RPD '),' REST ',' RST '),' RDGE ',' RDG '),' RIDGE ',' RDG '),' RDGS ',' RDG '),' RIDGES ',' RDG '),' RIVER ',' RIV '),' RIVR ',' RIV '),' RVR ',' RIV '),' ROAD ',' RD '),' RDS ',' RD '),' ROADS ',' RD '),' ROUTE ',' RTE '),' SHOAL ',' SHL '),' SHLS ',' SHL '),' SHOALS ',' SHL '),' SHOAR ',' SHR '),' SHORE ',' SHR '),' SHR ',' SHR '),' SHOARS ',' SHR '),' SHORES ',' SHR '),' SHRS ',' SHR '),' SKYWAY ',' SKWY '),' SPNG ',' SPG '),' SPRING ',' SPG '),' SPRNG ',' SPG '),' SPGS ',' SPG '),' SPNGS ',' SPG '),' SPRINGS ',' SPG '),' SPRNGS ',' SPG '),' SPURS ',' SPUR '),' SQR ',' SQ '),' SQRE ',' SQ '),' SQU ',' SQ '),' SQUARE ',' SQ '),' SQRS ',' SQ '),' SQUARES ',' SQ ') ,' SQS ',' SQ '),' STATION ',' STA '),' STATN ',' STA '),' STN ',' STA '),' STRAV ',' STRA '),' STRAVE ',' STRA '),' STRAVEN ',' STRA '),' STRAVENUE ',' STRA '),' STRAVN ',' STRA '),' STRVN ',' STRA '),' STRVNUE ',' STRA '),' STREAM ',' STRM '),' STREME ',' STRM '),' STR ',' ST '),' STREET ',' ST '),' STRT ',' ST '),' STREETS ',' ST '),' STS ',' ST '),' SUMIT ',' SMT '),' SUMITT ',' SMT '),' SUMMIT ',' SMT '),' TERR ',' TER '),' TERRACE ',' TER '),' THROUGHWAY ',' TRWY '),' TRACE ',' TRCE '),' TRACES ',' TRCE '),' TRACK ',' TRAK '),' TRACKS ',' TRAK '),' TRK ',' TRAK '),' TRKS ',' TRAK '),' TRAFFICWAY ',' TRFY '),' TR ',' TRL '),' TRAIL ',' TRL '),' TRAILS ',' TRL '),' TRLS ',' TRL '),' TUNEL ',' TUNL '),' TUNLS ',' TUNL '),' TUNNEL ',' TUNL '),' TUNNELS ',' TUNL '),' TUNNL ',' TUNL '),' TPK ',' TPKE '),' TRNPK ',' TPKE '),' TRPK ',' TPKE '),' TURNPIKE ',' TPKE '),' TURNPK ',' TPKE '),' UNDERPASS ',' UPAS '),' UNION ',' UN '),' UNIONS ',' UN '),' UNS ',' UN '),' VALLEY ',' VLY '),' VALLY ',' VLY '),' VLLY ',' VLY '),' VALLEYS ',' VLY ') ,' VLYS ',' VLY ') ,' VDCT ',' VIA '),' VIADCT ',' VIA '),' VIADUCT ',' VIA '),' VIEW ',' VW '),' VIEWS ',' VW '),' VWS ',' VW '),' VILL ',' VLG '),' VILLAG ',' VLG '),' VILLAGE ',' VLG '),' VILLG ',' VLG '),' VILLIAGE ',' VLG '),' VILLAGES ',' VLG '),' VLGS ',' VLG '),' VILLE ',' VL '),' VIST ',' VIS '),' VISTA ',' VIS '),' VST ',' VIS '),' VSTA ',' VIS '),' WALKS ',' WALK '),' WY ',' WAY '),' WAYS ',' WAY '),' WELL ',' WL '),' WELLS ',' WL '),' WLS ',' WL '),' APARTMENT ',' APT '),' BASEMENT ',' BSMT ') ,' BUILDING ',' BLDG '),' DEPARTMENT ',' DEPT '),' FLOOR ',' FL '),' FRONT ',' FRNT '),' HANGAR ',' HNGR '),' LOBBY ',' LBBY '),' LOWER ',' LOWR '),' OFFICE ',' OFC '),' PENTHOUSE ',' PH '),' ROOM ',' RM '),' SPACE ',' SPC '),' SUITE ',' STE '),' TRAILER ',' TRLR '),' UNT ',' UNIT '),' UPPER ',' UPPR ')

-- Turn any set of spaces into a single space. Why 13 2:1 replacements? Because 2^13 is 8192, just in excess of VARCHAR(8000), so we _know_ that any number of contiguous spaces is now one space.

-- Also, standardize the most common misspelled PO BOX types _after_ the spaces are collapsed.

-- single spaces remain at this point to allow for word separations

SET @String = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@String,' ',' '),' ',' '),' ',' '),' ',' '),' ',' '),' ',' '),' ',' '),' ',' '),' ',' '),' ',' '),' ',' '),' ',' '),' ',' '), ' po bx ',' PO BOX '), ' p o box ',' PO BOX '), ' P0 BOX ',' PO BOX ')