Address Abbreviations Fix

[hdsqltech]

Hi Folks,

I have a list of address abbreviations that I would need to fix; we need to fix all addresses.

If there’s an address with “765 N Main St” replace it with 765 N Main Street ( It is important that when changing the abbreviations we don’t lose the full address) If theres an address called 765 N Star St, make sure it doesn’t get the “st” in star and replace it like 765 N Street Street.
After all characters are first replaced, and if you find spaceSTspace, then replace it with Street, if you see it like this 765 N MainSt, then nothing there will be replaced.


Please igorne the numbers as this information was taken from an excel.
:::The following are the abbreviations to be used and changed to:::

13 ALY ALLEY
14 ANX ANNEX
15 APT APARTMENT
16 ARC ARCADE
17 AVE AVENUE
18 BSMT BASEMENT
19 BYU BAYOU
20 BCH BEACH
21 BND BEND
22 BLF BLUFF
23 BTM BOTTOM
24 BLVD BOULEVARD
25 BR BRANCH
26 BRG BRIDGE
27 BRK BROOK
28 BLDG BUILDING
29 BG BURG
30 BYP BYPASS
31 CP CAMP
32 CYN CANYON
33 CPE CAPE
34 CSWY CAUSEWAY
35 CTR CENTER
36 CIR CIRCLE
37 CLFS CLIFF
38 CLFS CLIFFS
39 CLB CLUB
40 COR CORNER
41 CORS CORNERS
42 CRSE COURSE
43 CT COURT
44 CTS COURTS
45 CV COVE
46 CRK CREEK
47 CRES CRESCENT
48 XING CROSSING
49 DL DALE
50 DM DAM
51 DEPT DEPARTMENT
52 DV DIVIDE
53 DR DRIVE
54 EST ESTATE
55 EXPY EXPRESSWAY
56 EXT EXTENSION
57 FLS FALLS
58 FRY FERRY
59 FLD FIELD
60 FLDS FIELDS
61 FLT FLAT
62 FL FLOOR
63 FRD FORD
64 FRST FOREST
65 FRG FORGE
66 FRK FORK
67 FRKS FORKS
68 FT FORT
69 FWY FREEWAY
70 FRNT FRONT
71 GDNS GARDEN
72 GDNS GARDENS
73 GTWY GATEWAY
74 GLN GLEN
75 GRN GREEN
76 GRV GROVE
77 HNGR HANGER
78 HBR HARBOR
79 HVN HAVEN
80 HTS HEIGHTS
81 HWY HIGHWAY
82 HL HILL
83 HLS HILLS
84 HOLW HOLLOW
85 INLT INLET
86 IS ISLAND
87 ISS ISLANDS
88 JCT JUNCTION
89 KY KEY
90 KNLS KNOLL
91 KNLS KNOLLS
92 LK LAKE
93 LKS LAKES
94 LNDG LANDING
95 LN LANE
96 LGT LIGHT
97 LF LOAF
98 LBBY LOBBY
99 LCKS LOCK
100 LCKS LOCKS
101 LDG LODGE
102 LOWR LOWER
103 MNR MANOR
104 MDWS MEADOW
105 MDWS MEADOWS
106 ML MILL
107 MLS MILLS
108 MSN MISSION
109 MT MOUNT
110 MTN MOUNTAIN
111 NCK NECK
112 OFC OFFICE
113 ORCH ORCHARD
114 PKWY PARKWAY
115 PH PENTHOUSE
116 PNES PINE
117 PNES PINES
118 PL PLACE
119 PLN PLAIN
120 PLNS PLAINS
121 PLZ PLAZA
122 PT POINT
123 PRT PORT
124 PR PRAIRIE
125 RADL RADIAL
126 RNCH RANCH
127 RPDS RAPID
128 RPDS RAPIDS
129 RST REST
130 RDG RIDGE
131 RIV RIVER
132 RD ROAD
133 RM ROOM
134 SHL SHOAL
135 SHLS SHOALS
136 SHR SHORE
137 SHRS SHORES
138 SPC SPACE
139 SPG SPRING
140 SPGS SPRINGS
141 SQ SQUARE
142 STA STATION
143 STRA STRAVENUE
144 STRM STREAM
145 ST STREET
146 STE SUITE
147 SMT SUMMIT
148 TER TERRACE
149 TRCE TRACE
150 TRAK TRACK
151 TRFY TRAFFICWAY
152 TRL TRAIL
153 TRLR TRAILER
154 TUNL TUNNEL
155 TPKE TURNPIKE
156 UN UNION
157 UPPR UPPER
158 VLY VALLEY
159 VIA VIADUCT
160 VW VIEW
161 VLG VILLAGE
162 VL VILLE
163 VIS VISTA
164 WAY WAY
165 WLS WELL
166 WLS WELLS

 

[ingresman]

Uaing "765 N Main St" and "765 N Star St" as examples.
I would add a space so you get "765 N Main St " and "765 N Star St " you can now do some updates to the data. I woud probalby write a PHP script to do it rather than use SQL.
You might get away with using the string index functions by...
update fred set line = (bytes before " st " + " street" + (bytes after " st ") where line like "% st %". (n.b. I got rid of the trailing space I added)
(I could look up the functions for you but I'm going home soom, the online manual will tell you under string function). Anyhow it's a bit like using Basic lots of mid() and concatanation. You might need to tinker with the like and the trainling space might not be needed if it's says like "% st" which should pick up the last st in the column.
Now the bad news, if you had an address like "74 st elsewhere st" it would pick up the first st, probs not what you need. So again look at the functions as I think there is a way to pick up ther lat occurance in a string.
Looking at your replacment cases st looks to be the one that might cause you trouble xo it might be worth not doing that one and perhaps doing it manualy. You culd run an SQL script to see which rows whuch have st but not at the end so would end up being corrupt.
How many do you have to do?, it might be worth while doing it with a small app and as few temps to over-key the correct values.
As I say at the start I'd use a program to do it, you'll get a lot more control. Data cleansing is always a nightmare.

 

[ingresman]

Had a litte play with:

update test set col1= concat(left(col1,length(col1)-3)," Street")  where ucase(right(col1,3)) = " ST";

Which seems to do the correct actions.
It's very general but I'm sure you see what is going on. You might have to do dome tailoring around things like UN UNION where UN might appear in many words. You'll probably end up with 166 scripts to do it. I think trying one would be too hard.
I'd be interested to see how you get on.