Using table for search and replace values
-
I have been searching, but have not been able to find a solution for this. Hoping someone on here can direct me towards the solution.
If I have a table like below:
And another table that had the following values in a field called "Name":
How would I be able to do a replace so that in the above example "Timothy Johnson" would be update to "Tim Johnson"?
The actual use case is not using names and are much longer strings without much logic that would allow splitting them into separate columns either.
-
Can a same row have multiple replacements? How long are the columns?
-
The same row can have multiple replacements and the column size can be whatever I set it to.
-
Maybe this will help?
You could use multiple REPLACE functions
use tempdb;
go
CREATE TABLE TheReplacements(
Search_Value VARCHAR(15) PRIMARY KEY
, Replace_Value VARCHAR(15) NOT NULL
);
GO
INSERT INTO TheReplacements (Search_Value, Replace_Value)
VALUES ('Timothy','Tim'),('Kathleen','Kathy'),('Joseph','Joe'),('Michael','Mike');
CREATE TABLE People (
PersonID INT IDENTITY
, FirstName VARCHAR(15) NOT NULL
, LastName VARCHAR(15) NOT NULL
);
GO
INSERT INTO People (FirstName, LastName)
VALUES ('Tim', 'Jones'),('Kathleen', 'Jones'),('Joseph', 'Jones'),('Frederick', 'Jones'),('Michael', 'Jones'),('John', 'Jones');
UPDATE People
SET FirstName = tr.Replace_Value
FROM TheReplacements tr
INNER JOIN People ON People.FirstName = tr.Search_Value -- you'd have to use LIKE here...
WHERE tr.Replace_Value IS NOT NULL; -
Try this:
SELECT * INTO #Values
FROM (
SELECT 'Timothy' AS Search_value, 'Tim' AS Replace_Val UNION ALL
SELECT 'Kathleen', 'Kathy' UNION ALL
SELECT 'Joseph', 'Joe' UNION ALL
SELECT 'Michael','Mike'
) AS Val
SELECT * INTO #Names
FROM (
SELECT 'Timothy Johnson Timothy' AS Name UNION ALL
SELECT 'Kathleen Phillips' UNION ALL
SELECT 'Joseph Walker' UNION ALL
SELECT 'Michael Rogers'
) AS N
UPDATE N SET Name = REPLACE(Name, Search_value, Replace_Val)
FROM #Names AS N
INNER JOIN #Values AS V
ON N.Name LIKE '%' + V.Search_value + '%'
SELECT * FROM #Names
SELECT * FROM #Values
DROP TABLE #Names
DROP TABLE #Values--Vadim R.
-
Ah, mine not going to work in one pass if there are different substrings from search_val, like this:
SELECT * INTO #Names
FROM (
SELECT 'Timothy Johnson Timothy' AS Name UNION ALL
SELECT 'Kathleen Phillips' UNION ALL
SELECT 'Joseph Walker Timothy' UNION ALL
SELECT 'Michael Rogers Joseph'
) AS N--Vadim R.
-
How many possible rows are going to be in the Search/Replace values table?
--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) - Jeff Moden wrote:
How many possible rows are going to be in the Search/Replace values table?
About 500.
-
Thanks. I've got to find some old code I wrote for fixing street addresses that did (IIRC) 400 replacements. I'll look for it after work tonight.
--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) - Jeff Moden wrote:
Thanks. I've got to find some old code I wrote for fixing street addresses that did (IIRC) 400 replacements. I'll look for it after work tonight.
-
Thank you. That is greatly appreciated.
-
Apologies... it took longer to find than expected.
The following code makes up to 450 possible substitutions. Details are in the code. Please understand that I've not tested the method I used on really wide strings because the requirement was to make redaction of postal codes a whole lot faster. The code is also necessarily a scalar function (not something I willingly do most of the time) because they wanted to use it in a persisted computed column. I don't recall the exact timings but I do recall that it was 3 orders of magnitude faster that what the 6 people (the previous 6 revs) wrote before me.
If you need something that's not all UPPER case, or the strings are huge, we'll have to try something else but wanted to introduce you to the principle here. (And, sorry... the forum software had some code alignment formatting problems).
ALTER FUNCTION [dbo].[ApplyStandardAbbreviationForBlockFieldScalarJBM]
/**********************************************************************************************************************
Purpose:
This function applies standard abbreviations for block fields to a string by looking for search terms and replacing
them with their abbreviations. It takes in a flag to indicate if the incoming string already has OCR corrections
applied to it. It also takes in a flag to indicate if it should replace non-USPS-approved terms.
Special Instructions:
NONE
Usage:
SELECT dbo.iim_ApplyStandardAbbreviationForBlockFieldScalar('hello expressway world north and west parkway is company',0,0);
Developer Notes:
1. Since the original intent of this code is to fix postal addresses in the U.S.A., the input string will be
converted to all UPPER CASE and will be returned as all UPPER CASE. The good part about that is that it allows
us to use a binary collation during the "Search'n'Replace" part of the code, which is a performance trick to
begin with.
Revision History:
Note: History prior to Rev 07 didn't matter and so I removed it and only left the latest rev, which is a total
rewrite of what was previously in the code except for the "monster" CTE that contains the "Search'n'Replace"
terms.
Rev 07 - 11 Aug 2015 - Jeff Moden
- Ticket 0012857: (CIP) Improve Performance of SignatureBlock Related Functions.
- (Part of the "Continuous Improvement Program" where I work).
- Total rewrite of the code that does the actual word replacements for performance and to simplify the code.
- Cleanup of code for readability.
- Added binary collation for extra performance improvement since everything is working with only UPPER Case.
- Note that this rewrite improved 10,000 uses from more than 10 minutes to about 11 seconds in production.
**********************************************************************************************************************/
(
@pBaseString VARCHAR(8000),
@pBaseStringHasOCRCorrections BIT,
@pApplyNonUSPSApprovedAbbreviations BIT
)
RETURNS VARCHAR(8000) WITH SCHEMABINDING AS
BEGIN
--===== Duplicate all spaces and add leading/trailing spaces so that multiple REPLACEs will work when
-- duplicate words are present. This also forces upper case to take advantage of binary collation
-- for performance. (Section added by Rev 07)
SELECT @pBaseString = ' '+REPLACE(UPPER(@pBaseString),' ',' ')+' '
;
WITH cteSearchReplace(ReplaceOCR, ReplaceStr, SearchStr, SearchOCR, USPSApproved)
AS
(--==== This CTE contains all of the OCR and Non-OCR Search/Replace values and a flag indicating
-- USPS Approval of the abbreviations.
SELECT'A1Y', 'ALY', 'ALLEE', 'A11EE', 1 UNION ALL
SELECT'A1Y', 'ALY', 'ALLEY', 'A11EY', 1 UNION ALL
SELECT'A1Y', 'ALY', 'ALLY', 'A11Y', 1 UNION ALL
SELECT'ANX', 'ANX', 'ANEX', 'ANEX', 1 UNION ALL
SELECT'ANX', 'ANX', 'ANNEX', 'ANNEX', 1 UNION ALL
SELECT'ANX', 'ANX', 'ANNX', 'ANNX', 1 UNION ALL
SELECT'ARC', 'ARC', 'ARCADE', 'ARCA0E',1 UNION ALL
SELECT'AUE', 'AVE', 'AV', 'AU', 1 UNION ALL
SELECT'AUE', 'AVE', 'AVEN', 'AUEN', 1 UNION ALL
SELECT'AUE', 'AVE', 'AVENU', 'AUENU', 1 UNION ALL
SELECT'AUE', 'AVE', 'AVENUE', 'AUENUE',1 UNION ALL
SELECT'AUE', 'AVE', 'AVN', 'AUN', 1 UNION ALL
SELECT'AUE', 'AVE', 'AVNUE', 'AUNUE', 1 UNION ALL
SELECT'8CH', 'BCH', 'BEACH', '8EACH', 1 UNION ALL
SELECT'86', 'BG', 'BURG', '8UR6', 1 UNION ALL
SELECT'86', 'BG', 'BURGS', '8UR65', 1 UNION ALL
SELECT'81F', 'BLF', 'BLUF', '81UF', 1 UNION ALL
SELECT'81F', 'BLF', 'BLUFF', '81UFF', 1 UNION ALL
SELECT'81F', 'BLF', 'BLUFFS', '81UFF5',1 UNION ALL
SELECT'81U0', 'BLVD', 'BLVE', '81UE', 1 UNION ALL
SELECT'81U0', 'BLVD', 'BOUL', '80U1', 1 UNION ALL
SELECT'81U0', 'BLVD', 'BOULEVARD','80U1EUAR0',1 UNION ALL
SELECT'81U0', 'BLVD', 'BOULV', '80U1U', 1 UNION ALL
SELECT'8N0', 'BND', 'BEND', '8EN0', 1 UNION ALL
SELECT'8R', 'BR', 'BRANCH', '8RANCH',1 UNION ALL
SELECT'8R', 'BR', 'BRNCH', '8RNCH', 1 UNION ALL
SELECT'8R6', 'BRG', 'BRDGE', '8R06E', 1 UNION ALL
SELECT'8R6', 'BRG', 'BRIDGE', '8R106E',1 UNION ALL
SELECT'8RK', 'BRK', 'BROOK', '8R00K', 1 UNION ALL
SELECT'8RK', 'BRK', 'BROOKS', '8R00K5',1 UNION ALL
SELECT'8RK', 'BRK', 'BRKS', '8RK5', 1 UNION ALL
SELECT'81M', 'BTM', 'BOT', '801', 1 UNION ALL
SELECT'81M', 'BTM', 'BOTTM', '8011M', 1 UNION ALL
SELECT'81M', 'BTM', 'BOTTOM', '80110M',1 UNION ALL
SELECT'8YP', 'BYP', 'BYPA', '8YPA', 1 UNION ALL
SELECT'8YP', 'BYP', 'BYPAS', '8YPA5', 1 UNION ALL
SELECT'8YP', 'BYP', 'BYPASS', '8YPA55',1 UNION ALL
SELECT'8YP', 'BYP', 'BYPS', '8YP5', 1 UNION ALL
SELECT'8YU', 'BYU', 'BAYOO', '8AY00', 1 UNION ALL
SELECT'8YU', 'BYU', 'BAYOU', '8AY0U', 1 UNION ALL
SELECT'C1R', 'CIR', 'CIRC', 'C1RC', 1 UNION ALL
SELECT'C1R', 'CIR', 'CIRCL', 'C1RC1', 1 UNION ALL
SELECT'C1R', 'CIR', 'CIRCLE', 'C1RC1E',1 UNION ALL
SELECT'C1R', 'CIR', 'CRCL', 'CRC1', 1 UNION ALL
SELECT'C1R', 'CIR', 'CRCLE', 'CRC1E', 1 UNION ALL
SELECT'C1R', 'CIR', 'CIRCLES', 'C1RC1E5',1 UNION ALL
SELECT'C1R', 'CIR', 'CIRS', 'C1R5', 1 UNION ALL
SELECT'C18', 'CLB', 'CLUB', 'C1U8', 1 UNION ALL
SELECT'C1F', 'CLF', 'CLIFF', 'C11FF', 1 UNION ALL
SELECT'C1F', 'CLF', 'CLFS', 'C1F5', 1 UNION ALL
SELECT'C1F', 'CLF', 'CLIFFS', 'C11FF5',1 UNION ALL
SELECT'CMN', 'CMN', 'COMMON', 'C0MM0N',1 UNION ALL
SELECT'CMN', 'CMN', 'COMMONS', 'C0MM0N5',1 UNION ALL
SELECT'CMN', 'CMN', 'CMNS', 'CMN5', 1 UNION ALL
SELECT'C0R', 'COR', 'CORNER', 'C0RNER',1 UNION ALL
SELECT'C0R', 'COR', 'CORS', 'C0R5', 1 UNION ALL
SELECT'C0R', 'COR', 'CORNERS', 'C0RNER5',1 UNION ALL
SELECT'CP', 'CP', 'CAMP', 'CAMP', 1 UNION ALL
SELECT'CP', 'CP', 'CMP', 'CMP', 1 UNION ALL
SELECT'CPE', 'CPE', 'CAPE', 'CAPE', 1 UNION ALL
SELECT'CRE5', 'CRES', 'CRESCENT', 'CRE5CEN1',1 UNION ALL
SELECT'CRE5', 'CRES', 'CRSENT', 'CR5EN1',1 UNION ALL
SELECT'CRE5', 'CRES', 'CRSNT', 'CR5N1', 1 UNION ALL
SELECT'CRK', 'CRK', 'CREEK', 'CREEK', 1 UNION ALL
SELECT'CR5E', 'CRSE', 'COURSE', 'C0UR5E',1 UNION ALL
SELECT'CR51', 'CRST', 'CREST', 'CRE51', 1 UNION ALL
SELECT'C5WY', 'CSWY', 'CAUSEWAY', 'CAU5EWAY',1 UNION ALL
SELECT'C5WY', 'CSWY', 'CAUSWAY', 'CAU5WAY',1 UNION ALL
SELECT'C1', 'CT', 'COURT', 'C0UR1', 1 UNION ALL
SELECT'C1R', 'CTR', 'CEN', 'CEN', 1 UNION ALL
SELECT'C1R', 'CTR', 'CENT', 'CEN1', 1 UNION ALL
SELECT'C1R', 'CTR', 'CENTER', 'CEN1ER',1 UNION ALL
SELECT'C1R', 'CTR', 'CENTR', 'CEN1R', 1 UNION ALL
SELECT'C1R', 'CTR', 'CENTRE', 'CEN1RE',1 UNION ALL
SELECT'C1R', 'CTR', 'CNTER', 'CN1ER', 1 UNION ALL
SELECT'C1R', 'CTR', 'CNTR', 'CN1R', 1 UNION ALL
SELECT'C1R', 'CTR', 'CENTERS', 'CEN1ER5',1 UNION ALL
SELECT'C1R', 'CTR', 'CTRS', 'C1R5', 1 UNION ALL
SELECT'C1', 'CT', 'COURTS', 'C0UR15',1 UNION ALL
SELECT'C1', 'CT', 'CTS', 'C15', 1 UNION ALL
SELECT'C1Y', 'CTY', 'CITY', 'C11Y', 1 UNION ALL
SELECT'C1Y', 'CTY', 'CITYS', 'C11Y5', 1 UNION ALL
SELECT'C1Y', 'CTY', 'CITIES', 'C111E5',1 UNION ALL
SELECT'CURU', 'CURV', 'CURVE', 'CURUE', 1 UNION ALL
SELECT'CU', 'CV', 'COVE', 'C0UE', 1 UNION ALL
SELECT'CU', 'CV', 'COVES', 'C0UE5', 1 UNION ALL
SELECT'CU', 'CV', 'CVS', 'CU5', 1 UNION ALL
SELECT'CYN', 'CYN', 'CANYN', 'CANYN', 1 UNION ALL
SELECT'CYN', 'CYN', 'CANYON', 'CANY0N',1 UNION ALL
SELECT'CYN', 'CYN', 'CNYN', 'CNYN', 1 UNION ALL
SELECT'1', 'DL', 'DALE', '0A1E', 1 UNION ALL
SELECT'0M', 'DM', 'DAM', '0AM', 1 UNION ALL
SELECT'0R', 'DR', 'DRIV', '0R1U', 1 UNION ALL
SELECT'0R', 'DR', 'DRIVE', '0R1UE', 1 UNION ALL
SELECT'0R', 'DR', 'DRV', '0RU', 1 UNION ALL
SELECT'0R', 'DR', 'DRIVES', '0R1UE5',1 UNION ALL
SELECT'0U', 'DV', 'DIV', '01U', 1 UNION ALL
SELECT'0U', 'DV', 'DIVIDE', '01U10E',1 UNION ALL
SELECT'0U', 'DV', 'DVD', '0U0', 1 UNION ALL
SELECT'E51', 'EST', 'ESTATE', 'E51A1E',1 UNION ALL
SELECT'E51', 'EST', 'ESTATES', 'E51A1E5',1 UNION ALL
SELECT'E51', 'EST', 'ESTS', 'E515', 1 UNION ALL
SELECT'EXPY', 'EXPY', 'EXP', 'EXP', 1 UNION ALL
SELECT'EXPY', 'EXPY', 'EXPR', 'EXPR', 1 UNION ALL
SELECT'EXPY', 'EXPY', 'EXPRESS', 'EXPRE55',1 UNION ALL
SELECT'EXPY', 'EXPY', 'EXPRESSWAY','EXPRE55WAY',1 UNION ALL
SELECT'EXPY', 'EXPY', 'EXPW', 'EXPW', 1 UNION ALL
SELECT'EX1', 'EXT', 'EXTENSION','EX1EN510N',1 UNION ALL
SELECT'EX1', 'EXT', 'EXTN', 'EX1N', 1 UNION ALL
SELECT'EX1', 'EXT', 'EXTNSN', 'EX1N5N', 1 UNION ALL
SELECT'EX1', 'EXT', 'EXTS', 'EX15', 1 UNION ALL
SELECT'EX1', 'EXT', 'EXTENSIONS','EX1EN510N5',1 UNION ALL
SELECT'F10', 'FLD', 'FIELD', 'F1E10', 1 UNION ALL
SELECT'F10', 'FLD', 'FIELDS', 'F1E105',1 UNION ALL
SELECT'F10', 'FLD', 'FLDS', 'F105', 1 UNION ALL
SELECT'F15', 'FLS', 'FALLS', 'FA115', 1 UNION ALL
SELECT'F15', 'FLS', 'FLS', 'F15', 1 UNION ALL
SELECT'F11', 'FLT', 'FLAT', 'F1A1', 1 UNION ALL
SELECT'F11', 'FLT', 'FLATS', 'F1A15', 1 UNION ALL
SELECT'F11', 'FLT', 'FLTS', 'F115', 1 UNION ALL
SELECT'FR0', 'FRD', 'FORD', 'F0R0', 1 UNION ALL
SELECT'FR0', 'FRD', 'FRD', 'FR0', 1 UNION ALL
SELECT'FR0', 'FRD', 'FORDS', 'F0R05', 1 UNION ALL
SELECT'FR0', 'FRD', 'FRDS', 'FR05', 1 UNION ALL
SELECT'FR6', 'FRG', 'FORG', 'F0R6', 1 UNION ALL
SELECT'FR6', 'FRG', 'FORGE', 'F0R6E', 1 UNION ALL
SELECT'FR6', 'FRG', 'FORGES', 'F0R6E5',1 UNION ALL
SELECT'FR6', 'FRG', 'FRGS', 'FR65', 1 UNION ALL
SELECT'FRK', 'FRK', 'FORK', 'F0RK', 1 UNION ALL
SELECT'FRK', 'FRK', 'FORKS', 'F0RK5', 1 UNION ALL
SELECT'FRK', 'FRK', 'FRKS', 'FRK5', 1 UNION ALL
SELECT'FR51', 'FRST', 'FOREST', 'F0RE51',1 UNION ALL
SELECT'FR51', 'FRST', 'FORESTS', 'F0RE515',1 UNION ALL
SELECT'FRY', 'FRY', 'FERRY', 'FERRY', 1 UNION ALL
SELECT'FRY', 'FRY', 'FRRY', 'FRRY', 1 UNION ALL
SELECT'F1', 'FT', 'FORT', 'F0R1', 1 UNION ALL
SELECT'F1', 'FT', 'FRT', 'FR1', 1 UNION ALL
SELECT'FWY', 'FWY', 'FREEWAY', 'FREEWAY',1 UNION ALL
SELECT'FWY', 'FWY', 'FREEWY', 'FREEWY',1 UNION ALL
SELECT'FWY', 'FWY', 'FRWAY', 'FRWAY', 1 UNION ALL
SELECT'FWY', 'FWY', 'FRWY', 'FRWY', 1 UNION ALL
SELECT'60N', 'GDN', 'GARDEN', '6AR0EN',1 UNION ALL
SELECT'60N', 'GDN', 'GARDN', '6AR0N', 1 UNION ALL
SELECT'60N', 'GDN', 'GRDEN', '6R0EN', 1 UNION ALL
SELECT'60N', 'GDN', 'GRDN', '6R0N', 1 UNION ALL
SELECT'60N', 'GDN', 'GARDENS', '6AR0EN5',1 UNION ALL
SELECT'60N', 'GDN', 'GDNS', '60N5', 1 UNION ALL
SELECT'60N', 'GDN', 'GRDNS', '6R0N5', 1 UNION ALL
SELECT'61N', 'GLN', 'GLEN', '61EN', 1 UNION ALL
SELECT'61N', 'GLN', 'GLENS', '61EN5', 1 UNION ALL
SELECT'61N', 'GLN', 'GLNS', '61N5', 1 UNION ALL
SELECT'6RN', 'GRN', 'GREEN', '6REEN', 1 UNION ALL
SELECT'6RN', 'GRN', 'GREENS', '6REEN5',1 UNION ALL
SELECT'6RU', 'GRV', 'GROV', '6R0U', 1 UNION ALL
SELECT'6RU', 'GRV', 'GROVE', '6R0UE', 1 UNION ALL
SELECT'6RU', 'GRV', 'GROVES', '6R0UE5',1 UNION ALL
SELECT'6RU', 'GRV', 'GRVS', '6RU5', 1 UNION ALL
SELECT'61WY', 'GTWY', 'GATEWAY', '6A1EWAY',1 UNION ALL
SELECT'61WY', 'GTWY', 'GATEWY', '6A1EWY',1 UNION ALL
SELECT'61WY', 'GTWY', 'GATWAY', '6A1WAY',1 UNION ALL
SELECT'61WY', 'GTWY', 'GTWAY', '61WAY', 1 UNION ALL
SELECT'H8R', 'HBR', 'HARB', 'HAR8', 1 UNION ALL
SELECT'H8R', 'HBR', 'HARBOR', 'HAR80R',1 UNION ALL
SELECT'H8R', 'HBR', 'HARBR', 'HAR8R', 1 UNION ALL
SELECT'H8R', 'HBR', 'HRBOR', 'HR80R', 1 UNION ALL
SELECT'H8R', 'HBR', 'HARBORS', 'HAR80R5',1 UNION ALL
SELECT'H8R', 'HBR', 'HBRS', 'H8R5', 1 UNION ALL
SELECT'H1', 'HL', 'HILL', 'H111', 1 UNION ALL
SELECT'H1', 'HL', 'HILLS', 'H1115', 1 UNION ALL
SELECT'H1', 'HL', 'HLS', 'H15', 1 UNION ALL
SELECT'H01W', 'HOLW', 'HLLW', 'H11W', 1 UNION ALL
SELECT'H01W', 'HOLW', 'HOLLOW', 'H0110W',1 UNION ALL
SELECT'H01W', 'HOLW', 'HOLLOWS', 'H0110W5',1 UNION ALL
SELECT'H01W', 'HOLW', 'HOLWS', 'H01W5', 1 UNION ALL
SELECT'H1', 'HT', 'HTS', 'H15', 1 UNION ALL
SELECT'H1', 'HT', 'HEIGHTS', 'HE16H15',1 UNION ALL
SELECT'HUN', 'HVN', 'HAVEN', 'HAUEN', 1 UNION ALL
SELECT'HWY', 'HWY', 'HIGHWAY', 'H16HWAY',1 UNION ALL
SELECT'HWY', 'HWY', 'HIGHWY', 'H16HWY',1 UNION ALL
SELECT'HWY', 'HWY', 'HIWAY', 'H1WAY', 1 UNION ALL
SELECT'HWY', 'HWY', 'HIWY', 'H1WY', 1 UNION ALL
SELECT'HWY', 'HWY', 'HWAY', 'HWAY', 1 UNION ALL
SELECT'1N11', 'INLT', 'INLET', '1N1E1', 1 UNION ALL
SELECT'15', 'IS', 'ISLAND', '151AN0',1 UNION ALL
SELECT'15', 'IS', 'ISLND', '151N0', 1 UNION ALL
SELECT'15', 'IS', 'ISLE', '151E', 1 UNION ALL
SELECT'15', 'IS', 'ISLES', '15100000',1 UNION ALL
SELECT'15', 'IS', 'ISLANDS', '151AN05',1 UNION ALL
SELECT'15', 'IS', 'ISLNDS', '151N05',1 UNION ALL
SELECT'15', 'IS', 'ISS', '155', 1 UNION ALL
SELECT'JC1', 'JCT', 'JCTION', 'JC110N',1 UNION ALL
SELECT'JC1', 'JCT', 'JCTN', 'JC1N', 1 UNION ALL
SELECT'JC1', 'JCT', 'JUNCTION', 'JUNC110N',1 UNION ALL
SELECT'JC1', 'JCT', 'JUNCTN', 'JUNC1N',1 UNION ALL
SELECT'JC1', 'JCT', 'JUNCTON', 'JUNC10N',1 UNION ALL
SELECT'JC1', 'JCT', 'JCTNS', 'JC1N5', 1 UNION ALL
SELECT'JC1', 'JCT', 'JCTS', 'JC15', 1 UNION ALL
SELECT'JC1', 'JCT', 'JUNCTIONS','JUNC110N5',1 UNION ALL
SELECT'KN1', 'KNL', 'KNOL', 'KN01', 1 UNION ALL
SELECT'KN1', 'KNL', 'KNOLL', 'KN011', 1 UNION ALL
SELECT'KN1', 'KNL', 'KNLS', 'KN15', 1 UNION ALL
SELECT'KN1', 'KNL', 'KNOLLS', 'KN0115',1 UNION ALL
SELECT'KY', 'KY', 'KEY', 'KEY', 1 UNION ALL
SELECT'KY', 'KY', 'KEYS', 'KEY5', 1 UNION ALL
SELECT'KY', 'KY', 'KYS', 'KY5', 1 UNION ALL
SELECT'1CK', 'LCK', 'LOCK', '10CK', 1 UNION ALL
SELECT'1CK', 'LCK', 'LCKS', '1CK5', 1 UNION ALL
SELECT'1CK', 'LCK', 'LOCKS', '10CK5', 1 UNION ALL
SELECT'106', 'LDG', 'LDGE', '106E', 1 UNION ALL
SELECT'106', 'LDG', 'LODG', '1006', 1 UNION ALL
SELECT'106', 'LDG', 'LODGE', '1006E', 1 UNION ALL
SELECT'1F', 'LF', 'LOAF', '10AF', 1 UNION ALL
SELECT'161', 'LGT', 'LIGHT', '116H1', 1 UNION ALL
SELECT'161', 'LGT', 'LIGHTS', '116H15',1 UNION ALL
SELECT'161', 'LGT', 'LGTS', '1615', 1 UNION ALL
SELECT'1K', 'LK', 'LAKE', '1AKE', 1 UNION ALL
SELECT'1K', 'LK', 'LAKES', '1AKE5', 1 UNION ALL
SELECT'1K', 'LK', 'LKS', '1K5', 1 UNION ALL
SELECT'1N', 'LN', 'LANE', '1ANE', 1 UNION ALL
SELECT'1N06', 'LNDG', 'LANDING', '1AN01N6',1 UNION ALL
SELECT'1N06', 'LNDG', 'LNDNG', '1N0N6', 1 UNION ALL
SELECT'100P', 'LOOP', 'LOOPS', '100P5', 1 UNION ALL
SELECT'M0W', 'MDW', 'MEADOW', 'MEA00W',1 UNION ALL
SELECT'M0W', 'MDW', 'MEADOWS', 'MEA00W5',1 UNION ALL
SELECT'M0W', 'MDW', 'MDWS', 'M0W5', 1 UNION ALL
SELECT'M0W', 'MDW', 'MEDOWS', 'ME00W5',1 UNION ALL
SELECT'MEW5', 'MEWS', 'MEWS', 'MEW5', 1 UNION ALL
SELECT'M1', 'ML', 'MILL', 'M111', 1 UNION ALL
SELECT'M1', 'ML', 'MILLS', 'M1115', 1 UNION ALL
SELECT'MNR', 'MNR', 'MANOR', 'MAN0R', 1 UNION ALL
SELECT'MNR', 'MNR', 'MANORS', 'MAN0R5',1 UNION ALL
SELECT'MNR', 'MNR', 'MNRS', 'MNR5', 1 UNION ALL
SELECT'M5N', 'MSN', 'MISSN', 'M155N', 1 UNION ALL
SELECT'M5N', 'MSN', 'MSSN', 'M55N', 1 UNION ALL
SELECT'M5N', 'MSN', 'MISSION', 'M15510N',1 UNION ALL
SELECT'M1', 'MT', 'MNT', 'MN1', 1 UNION ALL
SELECT'M1', 'MT','MOUNT', 'M0UN1', 1 UNION ALL
SELECT'M1', 'MT', 'MNTAIN', 'MN1A1N',1 UNION ALL
SELECT'M1', 'MT', 'MNTN', 'MN1N', 1 UNION ALL
SELECT'M1', 'MT', 'MOUNTAIN', 'M0UN1A1N',1 UNION ALL
SELECT'M1', 'MT', 'MOUNTIN', 'M0UN11N',1 UNION ALL
SELECT'M1', 'MT', 'MTIN', 'M11N', 1 UNION ALL
SELECT'M1', 'MT', 'MNTNS', 'MN1N5', 1 UNION ALL
SELECT'M1', 'MT', 'MOUNTAINS','M0UN1A1N5',1 UNION ALL
SELECT'M1WY', 'MTWY', 'MOTORWAY', 'M010RWAY',1 UNION ALL
SELECT'NCK', 'NCK', 'NECK', 'NECK', 1 UNION ALL
SELECT'0PA5', 'OPAS', 'OVERPASS', '0UERPA55', 1 UNION ALL
SELECT'0RCH', 'ORCH', 'ORCHARD', '0RCHAR0', 1 UNION ALL
SELECT'0RCH', 'ORCH', 'ORCHRD', '0RCHR0', 1 UNION ALL
SELECT'0UA1', 'OVAL', 'OVL', '0U1', 1 UNION ALL
SELECT'PARK', 'PARK', 'PARKS', 'PARK5', 1 UNION ALL
SELECT'PARK', 'PARK', 'PRK', 'PRK', 1 UNION ALL
SELECT'PA55', 'PASS', 'PASS', 'PA55', 1 UNION ALL
SELECT'PA1H', 'PATH', 'PATH', 'PA1H', 1 UNION ALL
SELECT'PA1H', 'PATH', 'PATHS', 'PA1H5', 1 UNION ALL
SELECT'P1KE', 'PIKE', 'PIKE', 'P1KE', 1 UNION ALL
SELECT'P1KE', 'PIKE', 'PIKES', 'P1KE5', 1 UNION ALL
SELECT'PKWY', 'PKWY', 'PARKWAY', 'PARKWAY', 1 UNION ALL
SELECT'PKWY', 'PKWY', 'PARKWAYS', 'PARKWAY5', 1 UNION ALL
SELECT'PKWY', 'PKWY', 'PARKWY', 'PARKWY', 1 UNION ALL
SELECT'PKWY', 'PKWY', 'PKWAY', 'PKWAY', 1 UNION ALL
SELECT'PKWY', 'PKWY', 'PKWYS', 'PKWY5', 1 UNION ALL
SELECT'PKWY', 'PKWY', 'PKY', 'PKY', 1 UNION ALL
SELECT'P1', 'PL', 'PLACE', 'P1ACE', 1 UNION ALL
SELECT'P1N', 'PLN', 'PLAIN', 'P1A1N', 1 UNION ALL
SELECT'P1N', 'PLN', 'PLAINS', 'P1A1N5',1 UNION ALL
SELECT'P1N', 'PLN', 'PLNS', 'P1N5', 1 UNION ALL
SELECT'P12', 'PLZ', 'PLAZA', 'P1A2A', 1 UNION ALL
SELECT'P12', 'PLZ', 'PLZA', 'P12A', 1 UNION ALL
SELECT'PNE', 'PNE', 'PINE', 'P1NE', 1 UNION ALL
SELECT'PNE', 'PNE', 'PINES', 'P1NE5', 1 UNION ALL
SELECT'PNE', 'PNE', 'PNES', 'PNE5', 1 UNION ALL
SELECT'PR', 'PR', 'PRAIRIE', 'PRA1R1E',1 UNION ALL
SELECT'PR', 'PR', 'PRR', 'PRR', 1 UNION ALL
SELECT'PR1', 'PRT', 'PORT', 'P0R1', 1 UNION ALL
SELECT'PR1', 'PRT', 'PORTS', 'P0R15', 1 UNION ALL
SELECT'PR1', 'PRT', 'PRTS', 'PR15', 1 UNION ALL
SELECT'P56E', 'PSGE', 'PASSAGE', 'PA55A6E',1 UNION ALL
SELECT'P1', 'PT', 'POINT', 'P01N1', 1 UNION ALL
SELECT'P1', 'PT', 'POINTS', 'P01N15',1 UNION ALL
SELECT'P1', 'PT', 'PTS', 'P15', 1 UNION ALL
SELECT'RA01', 'RADL', 'RAD', 'RA0', 1 UNION ALL
SELECT'RA01', 'RADL', 'RADIAL', 'RA01A1',1 UNION ALL
SELECT'RA01', 'RADL', 'RADIEL', 'RA01E1',1 UNION ALL
SELECT'R0', 'RD', 'ROAD', 'R0A0', 1 UNION ALL
SELECT'R06', 'RDG', 'RDGE', 'R06E', 1 UNION ALL
SELECT'R06', 'RDG', 'RIDGE', 'R106E', 1 UNION ALL
SELECT'R06', 'RDG', 'RDGS', 'R065', 1 UNION ALL
SELECT'R06', 'RDG', 'RIDGES', 'R106E5',1 UNION ALL
SELECT'R0', 'RD', 'RDS', 'R05', 1 UNION ALL
SELECT'R0', 'RD', 'ROADS', 'R0A05', 1 UNION ALL
SELECT'R1U', 'RIV', 'RIVER', 'R1UER', 1 UNION ALL
SELECT'R1U', 'RIV', 'RIVR', 'R1UR', 1 UNION ALL
SELECT'R1U', 'RIV', 'RVR', 'RUR', 1 UNION ALL
SELECT'RNCH', 'RNCH', 'RANCH', 'RANCH', 1 UNION ALL
SELECT'RNCH', 'RNCH', 'RANCHES', 'RANCHE5',1 UNION ALL
SELECT'RNCH', 'RNCH', 'RNCHS', 'RNCH5', 1 UNION ALL
SELECT'RP0', 'RPD', 'RAPID', 'RAP10', 1 UNION ALL
SELECT'RP0', 'RPD', 'RAPIDS', 'RAP105',1 UNION ALL
SELECT'RP0', 'RPD', 'RPDS', 'RP05', 1 UNION ALL
SELECT'R51', 'RST', 'REST', 'RE51', 1 UNION ALL
SELECT'R1E', 'RTE', 'ROUTE', 'R0U1E', 1 UNION ALL
SELECT'5H1', 'SHL', 'SHOAL', '5H0A1', 1 UNION ALL
SELECT'5H1', 'SHL', 'SHLS', '5H15', 1 UNION ALL
SELECT'5H1', 'SHL', 'SHOALS', '5H0A15',1 UNION ALL
SELECT'5HR', 'SHR', 'SHOAR', '5H0AR', 1 UNION ALL
SELECT'5HR', 'SHR', 'SHORE', '5H0RE', 1 UNION ALL
SELECT'5HR', 'SHR', 'SHOARS', '5H0AR5',1 UNION ALL
SELECT'5HR', 'SHR', 'SHORES', '5H0RE5',1 UNION ALL
SELECT'5HR', 'SHR', 'SHRS', '5HR5', 1 UNION ALL
SELECT'5KWY', 'SKWY', 'SKYWAY', '5KYWAY',1 UNION ALL
SELECT'5M1', 'SMT', 'SUMIT', '5UM11', 1 UNION ALL
SELECT'5M1', 'SMT', 'SUMITT', '5UM111',1 UNION ALL
SELECT'5M1', 'SMT', 'SUMMIT', '5UMM11',1 UNION ALL
SELECT'5P6', 'SPG', 'SPNG', '5PN6', 1 UNION ALL
SELECT'5P6', 'SPG', 'SPRING', '5PR1N6',1 UNION ALL
SELECT'5P6', 'SPG', 'SPRNG', '5PRN6', 1 UNION ALL
SELECT'5P6', 'SPG', 'SPGS', '5P65', 1 UNION ALL
SELECT'5P6', 'SPG', 'SPNGS', '5PN65', 1 UNION ALL
SELECT'5P6', 'SPG', 'SPRINGS', '5PR1N65',1 UNION ALL
SELECT'5P6', 'SPG', 'SPRNGS', '5PRN65',1 UNION ALL
SELECT'5PUR', 'SPUR', 'SPURS', '5PUR5', 1 UNION ALL
SELECT'50', 'SQ', 'SQR', '50R', 1 UNION ALL
SELECT'50', 'SQ', 'SQRE', '50RE', 1 UNION ALL
SELECT'50', 'SQ', 'SQU', '50U', 1 UNION ALL
SELECT'50', 'SQ', 'SQUARE', '50UARE',1 UNION ALL
SELECT'50', 'SQ', 'SQRS', '50R5', 1 UNION ALL
SELECT'50', 'SQ', 'SQUARES', '50UARE5',1 UNION ALL
SELECT'51', 'ST', 'STR', '51R', 1 UNION ALL
SELECT'51', 'ST', 'STREET', '51REE1',1 UNION ALL
SELECT'51', 'ST', 'STRT', '51R1', 1 UNION ALL
SELECT'51A', 'STA', 'STATION', '51A110N',1 UNION ALL
SELECT'51A', 'STA', 'STATN', '51A1N', 1 UNION ALL
SELECT'51A', 'STA', 'STN', '51N', 1 UNION ALL
SELECT'51RA', 'STRA', 'STRAV', '51RAU', 1 UNION ALL
SELECT'51RA', 'STRA', 'STRAVEN', '51RAUEN',1 UNION ALL
SELECT'51RA', 'STRA', 'STRAVENUE','51RAUENUE',1 UNION ALL
SELECT'51RA', 'STRA', 'STRAVN', '51RAUN',1 UNION ALL
SELECT'51RA', 'STRA', 'STRVN', '51RUN', 1 UNION ALL
SELECT'51RA', 'STRA', 'STRVNUE', '51RUNUE',1 UNION ALL
SELECT'51RM', 'STRM', 'STREAM', '51REAM',1 UNION ALL
SELECT'51RM', 'STRM', 'STREME', '51REME',1 UNION ALL
SELECT'51', 'ST', 'STREETS', '51REE15',1 UNION ALL
SELECT'1ER', 'TER', 'TERR', '1ERR', 1 UNION ALL
SELECT'1ER', 'TER', 'TERRACE', '1ERRACE',1 UNION ALL
SELECT'1PKE', 'TPKE', 'TRNPK', '1RNPK', 1 UNION ALL
SELECT'1PKE', 'TPKE', 'TURNPIKE', '1URNP1KE',1 UNION ALL
SELECT'1PKE', 'TPKE', 'TURNPK', '1URNPK',1 UNION ALL
SELECT'1RAK', 'TRAK', 'TRACK', '1RACK', 1 UNION ALL
SELECT'1RAK', 'TRAK', 'TRACKS', '1RACK5',1 UNION ALL
SELECT'1RAK', 'TRAK', 'TRK', '1RK', 1 UNION ALL
SELECT'1RAK', 'TRAK', 'TRKS', '1RK5', 1 UNION ALL
SELECT'1RCE', 'TRCE', 'TRACE', '1RACE', 1 UNION ALL
SELECT'1RCE', 'TRCE', 'TRACES', '1RACE5',1 UNION ALL
SELECT'1RFY', 'TRFY', 'TRAFFICWAY','1RAFF1CWAY',1 UNION ALL
SELECT'1R1', 'TRL', 'TRAIL', '1RA11', 1 UNION ALL
SELECT'1R1', 'TRL', 'TRAILS', '1RA115',1 UNION ALL
SELECT'1R1', 'TRL', 'TRLS', '1R15', 1 UNION ALL
SELECT'1R1R', 'TRLR', 'TRAILER', '1RA11ER',1 UNION ALL
SELECT'1R1R', 'TRLR', 'TRLRS', '1R1R5', 1 UNION ALL
SELECT'1RWY', 'TRWY', 'THROUGHWAY','1HR0U6HWAY',1 UNION ALL
SELECT'1UN1', 'TUNL', 'TUNEL', '1UNE1', 1 UNION ALL
SELECT'1UN1', 'TUNL', 'TUNLS', '1UN15', 1 UNION ALL
SELECT'1UN1', 'TUNL', 'TUNNEL', '1UNNE1',1 UNION ALL
SELECT'1UN1', 'TUNL', 'TUNNELS', '1UNNE15',1 UNION ALL
SELECT'1UN1', 'TUNL', 'TUNNL', '1UNN1', 1 UNION ALL
SELECT'1WP', 'TWP', 'TOWNSHIP', '10WN5H1P',1 UNION ALL
SELECT'1WP', 'TWP', 'TWNSHP', '1WN5HP',1 UNION ALL
SELECT'1WP', 'TWP', 'TWNSHIP', '1WN5H1P',1 UNION ALL
SELECT'1WP', 'TWP', 'TOWNSHP', '10WN5HP',1 UNION ALL
SELECT'UN', 'UN', 'UNION', 'UN10N', 1 UNION ALL
SELECT'UN', 'UN', 'UNIONS', 'UN10N5',1 UNION ALL
SELECT'UN', 'UN', 'UNS', 'UN5', 1 UNION ALL
SELECT'UPA5', 'UPAS', 'UNDERPASS','UN0ERPA55',1 UNION ALL
SELECT'U1A', 'VIA', 'VDCT', 'U0C1', 1 UNION ALL
SELECT'U1A', 'VIA', 'VIADCT', 'U1A0C1',1 UNION ALL
SELECT'U1A', 'VIA', 'VIADUCT', 'U1A0UC1',1 UNION ALL
SELECT'U15', 'VIS', 'VIST', 'U151', 1 UNION ALL
SELECT'U15', 'VIS', 'VISTA', 'U151A', 1 UNION ALL
SELECT'U15', 'VIS', 'VST', 'U51', 1 UNION ALL
SELECT'U15', 'VIS', 'VSTA', 'U51A', 1 UNION ALL
SELECT'U1', 'VL', 'VILLE', 'U111E', 1 UNION ALL
SELECT'U16', 'VLG', 'VILL', 'U111', 1 UNION ALL
SELECT'U16', 'VLG', 'VILLAG', 'U111A6',1 UNION ALL
SELECT'U16', 'VLG', 'VILLAGE', 'U111A6E',1 UNION ALL
SELECT'U16', 'VLG', 'VILLG', 'U1116', 1 UNION ALL
SELECT'U16', 'VLG', 'VILLIAGE', 'U1111A6E',1 UNION ALL
SELECT'U16', 'VLG', 'VILLAGES', 'U111A6E5',1 UNION ALL
SELECT'U16', 'VLG', 'VLGS', 'U165', 1 UNION ALL
SELECT'U1Y', 'VLY', 'VALLEY', 'UA11EY',1 UNION ALL
SELECT'U1Y', 'VLY', 'VALLY', 'UA11Y', 1 UNION ALL
SELECT'U1Y', 'VLY', 'VLLY', 'U11Y', 1 UNION ALL
SELECT'U1Y', 'VLY', 'VALLEYS', 'UA11EY5',1 UNION ALL
SELECT'U1Y', 'VLY', 'VLYS', 'U1Y5', 1 UNION ALL
SELECT'UW', 'VW', 'VIEW', 'U1EW', 1 UNION ALL
SELECT'UW', 'VW', 'VIEWS', 'U1EW5', 1 UNION ALL
SELECT'UW', 'VW', 'VWS', 'UW5', 1 UNION ALL
SELECT'WA1K', 'WALK', 'WALKS', 'WA1K5', 1 UNION ALL
SELECT'WAY', 'WAY', 'WY', 'WY', 1 UNION ALL
SELECT'WAY', 'WAY', 'WAYS', 'WAY5', 1 UNION ALL
SELECT'WAY', 'WAY', 'WYS', 'WY5', 1 UNION ALL
SELECT'W1', 'WL', 'WELL', 'WE11', 1 UNION ALL
SELECT'W1', 'WL', 'WELLS', 'WE115', 1 UNION ALL
SELECT'W1', 'WL', 'WLS', 'W15', 1 UNION ALL
SELECT'X1N6', 'XING', 'CROSSING', 'CR0551N6',1 UNION ALL
SELECT'X1N6', 'XING', 'CRSSING', 'CR551N6',1 UNION ALL
SELECT'X1N6', 'XING', 'CRSSNG', 'CR55N6',1 UNION ALL
SELECT'X1N6', 'XING', 'XING', 'X1N6', 1 UNION ALL
SELECT'XR0', 'XRD', 'CROSSROAD','CR055R0A0',1 UNION ALL
SELECT'XR0', 'XRD', 'CROSSROADS','CR055R0A05',1 UNION ALL
SELECT'E', 'E', 'EAST', 'EA51', 1 UNION ALL
SELECT'N', 'N', 'NORTH', 'N0R1H', 1 UNION ALL
SELECT'NE', 'NE', 'NORTHEAST','N0R1HEA51',1 UNION ALL
SELECT'NW', 'NW', 'NORTHWEST','N0R1HWE51',1 UNION ALL
SELECT'5', 'S', 'SOUTH', '50U1H', 1 UNION ALL
SELECT'5E', 'SE', 'SOUTHEAST','50U1HEA51',1 UNION ALL
SELECT'5W', 'SW', 'SOUTHWEST','50U1HWE51',1 UNION ALL
SELECT'W', 'W', 'WEST', 'WE51', 1 UNION ALL
SELECT'#', '#', 'APT', 'AP1', 1 UNION ALL
SELECT'#', '#', 'APARTMENT','APAR1MEN1',1 UNION ALL
SELECT'#', '#', 'BASEMENT', '8A5EMEN1', 1 UNION ALL
SELECT'#', '#', 'BUILDING', '8U1101N6',1 UNION ALL
SELECT'#', '#', 'BLDG', '8106', 1 UNION ALL
SELECT'#', '#', 'DEPARTMENT','0EPAR1MEN1',1 UNION ALL
SELECT'#', '#', 'FLOOR', 'F100R', 1 UNION ALL
SELECT'#', '#', 'FRONT', 'FR0N1', 1 UNION ALL
SELECT'#', '#', 'HANGAR', 'HAN6AR',1 UNION ALL
SELECT'#', '#', 'LOBBY', '1088Y', 1 UNION ALL
SELECT'#', '#', 'LOT', '101', 1 UNION ALL
SELECT'#', '#', 'LOWER', '10WER', 1 UNION ALL
SELECT'#', '#', 'NBR', 'N8R', 1 UNION ALL
SELECT'#', '#', 'NUM', 'NUM', 1 UNION ALL
SELECT'#', '#', 'NUMBER', 'NUM8ER',1 UNION ALL
SELECT'#', '#', 'OFFICE', '0FF1CE',1 UNION ALL
SELECT'#', '#', 'PENTHOUSE','PEN1H0U5E',1 UNION ALL
SELECT'#', '#', 'PIER', 'P1ER', 1 UNION ALL
SELECT'#', '#', 'REAR', 'REAR', 1 UNION ALL
SELECT'#', '#', 'ROOM', 'R00M', 1 UNION ALL
SELECT'#', '#', 'SIDE', '510E', 1 UNION ALL
SELECT'#', '#', 'SLIP', '511P', 1 UNION ALL
SELECT'#', '#', 'SPACE', '5PACE', 1 UNION ALL
SELECT'#', '#', 'STOP', '510P', 1 UNION ALL
SELECT'#', '#', 'SUITE', '5U11E', 1 UNION ALL
SELECT'#', '#', 'UNIT', 'UN11', 1 UNION ALL
SELECT'#', '#', 'UPPER', 'UPPER', 1 UNION ALL
SELECT'51', 'ST', 'SAINT', '5A1N1', 1 UNION ALL
SELECT'51', 'ST', 'SAINTE', '5A1N1E',1 UNION ALL
SELECT'&', '&', 'AND', 'AN0', 0 UNION ALL
SELECT'C0', 'CO', 'COMPANY', 'C0MPANY',0 UNION ALL
SELECT'1NC', 'INC', 'INCORPORATED','1NC0RP0RA1E0', 0
)--==== The following single SELECT replaced a MUCH slower WHILE loop and a ton of code.
-- Since this function was required to be a scalar function so that it could be persisted in a computed column,
-- no attempt was made to make it work with more than just one input at a time. Even then, you won't be
-- seriously disappointed in the performance of this scalar function.
-- Note that the binary collation is also important to performance here.
SELECT @pBaseString = REPLACE(--===== The CASE functions decide what to use for Search/Replace depending on OCR settings
-- (Section rewritten by Rev 07)
@pBaseString COLLATE LATIN1_GENERAL_BIN
,CASE
WHEN @pBaseStringHasOCRCorrections = 1
THEN ' '+SearchOCR+' ' COLLATE LATIN1_GENERAL_BIN
ELSE ' '+SearchStr+' ' COLLATE LATIN1_GENERAL_BIN
END
,CASE
WHEN @pBaseStringHasOCRCorrections = 1
THEN ' '+ReplaceOCR+' ' COLLATE LATIN1_GENERAL_BIN
ELSE ' '+ReplaceStr+' ' COLLATE LATIN1_GENERAL_BIN
END
)
FROM cteSearchReplace
WHERE (USPSApproved = 1 OR @pApplyNonUSPSApprovedAbbreviations = 1)
;
--===== Trim leading/trailing spaces and deduplicate previously duplicated spaces (Section added by Rev 07)
SELECT @pBaseString = REPLACE(LTRIM(RTRIM(@pBaseString)),' ',' ')
;
--===== Return the result
RETURN @pBaseString;
END
;--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)
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply