Search code examples
sqlsql-serverstringreplacedata-cleaning

SQL Server replacing only abbreviation within a string

Within SQL Server I am cleaning strings to remove abbreviations within a street name: 

-- Replace cl, cl., cl , with Close
SELECT @CleanedAddress = REPLACE(@CleanedAddress, 'cl', ' Close')
WHERE  @CleanedAddress LIKE '% cl'

SELECT @CleanedAddress = REPLACE(@CleanedAddress, ' cl.', ' Close')
WHERE  @CleanedAddress LIKE '% cl.'

SELECT @CleanedAddress = REPLACE(@CleanedAddress, ' cl ', ' Close ')
WHERE  @CleanedAddress LIKE '% cl %'

SELECT @CleanedAddress = REPLACE(@CleanedAddress, ' cl. ', ' Close ')
WHERE  @CleanedAddress LIKE '% cl. %'

Given an example street name of 12 Closet Close written as 12 Closet Cl I can successfully match using the following:

SELECT @CleanedAddress = REPLACE(@CleanedAddress, 'cl', ' Close')
WHERE  @CleanedAddress LIKE '% cl'

However, the replace changes the all occurrences of 'cl' resulting in 12 Closeoset Close

How can I replace the ending "Cl" with "Close" rather than all occurrences of "CL" and apply this to work with thousands of strings that might end with "CL"?

Solution

  • You could try a RIGHT string instead, I've specifically gone for right 3, so that you would look for a space followed by the cl - This prevents it picking up on any road name ending 'cl'. 

    SELECT @CleanedAddress = LEFT(@CleanedAddress, len(@CleanedAddress) -3) + ' Close'
WHERE  Right(@CleanedAddress,3) = ' cl'

    Your other instances where you were already looking for a '% cl. %' would work as they are, its just that first one which is more likely to provide false matches