asked
Update part of address
I have a street address like: 2015 S 3RD Street. I would like to change the 3RD part of address to 3rd. So, any address with 1ST, 2ND, 3RD, 4TH, etc. any part with ST, ND, RD and TH change to lowercase but only after the number in the alphanumeric. Can't just use replace as ...Replace(address, 'RD', 'rd')...because it would make Road in lowercase. 215TH = 215th. any number with this suffix. Thanks!
I believe that instead of writing a complex case statement attempting to take care of all upper case occurrences of some number followed by ST, ND, RD or TH, it would be simpler to take care of them one at a time, thus writing and executing 4 update statements. Here are the statements which should work: -- Update record which include dTH as a part of street address value -- where d denotes any number. This update replaces TH with th. ;with records as ( select StreetAddress, patindex('%[0-9]TH %', StreetAddress collate SQL_Latin1_General_CP1_CS_AS) pin from YourTable where patindex('%[0-9]TH %', StreetAddress collate SQL_Latin1_General_CP1_CS_AS) > 0 ) update records set StreetAddress = substring(StreetAddress, 1, pin) + 'th' + substring(StreetAddress, pin + 3, 100); -- update all dST with dst ;with records as ( select StreetAddress, patindex('%[0-9]ST %', StreetAddress collate SQL_Latin1_General_CP1_CS_AS) pin from YourTable where patindex('%[0-9]ST %', StreetAddress collate SQL_Latin1_General_CP1_CS_AS) > 0 ) update records set StreetAddress = substring(StreetAddress, 1, pin) + 'st' + substring(StreetAddress, pin + 3, 100); -- update all dND with dnd ;with records as ( select StreetAddress, patindex('%[0-9]ND %', StreetAddress collate SQL_Latin1_General_CP1_CS_AS) pin from YourTable where patindex('%[0-9]ND %', StreetAddress collate SQL_Latin1_General_CP1_CS_AS) > 0 ) update records set StreetAddress = substring(StreetAddress, 1, pin) + 'nd' + substring(StreetAddress, pin + 3, 100); -- update all dRD with drd ;with records as ( select StreetAddress, patindex('%[0-9]RD %', StreetAddress collate SQL_Latin1_General_CP1_CS_AS) pin from YourTable where patindex('%[0-9]RD %', StreetAddress collate SQL_Latin1_General_CP1_CS_AS) > 0 ) update records set StreetAddress = substring(StreetAddress, 1, pin) + 'rd' + substring(StreetAddress, pin + 3, 100); go If there is a need to replace mixed case occurrences as well (such as 1St, 2nD, etc) then you can relax the scripts a bit by removing the collation references, this will still work as expected though it will include the faux updates to those rows which already have the suffixes spelled correctly (in lower case). Hope this helps. Oleg
18 People are following this question.
