I have an address column that needs to be formatted.
There are 20+ updates that would usually take place, an example of which can be found below:
'COURT':'CT'
'STREET':'ST'
'PLACE':'PL'
'DRIVE':'DR'
'AVENUE':'AVE'
'ROAD':'RD'
'TERRACE':'TER'
'LANE':'LN'
'HIGHWAY':'HWY'
'BOULEVARD':'BLVD'
'NORTH':'N'
'EAST':'E'
'SOUTH':'S'
'WEST':'W'
I would usually run multiple UPDATE staements such as:
SET [Address] = REPLACE([Address],'Street', 'ST')
WHERE CHARINDEX('Street', [Address]) > 0
How would I perform these updates in a âdbt wayâ?
CASE WHEN has been suggested in the documentation: UPDATES
I donât believe CASE WHEN is suitable as a single row may require multiple updates.
I could use a ridiculously long REPLACE statements :
REPALCE(
REPLACE(
REPLACE(
REPLACE(Address,'COURT','CT'),
'STREET','ST'),
'PLACE','PL'),
'DRIVE','DR'),.......
Are there any alternatives to the long REPLACE statement, am I missing something?