Permits staff must scrub OCR noise from address lines with a regex replacement before geocoding. Which function should transform the text?
Select an answer to reveal the explanation.
Short Explanation
REGEXP_REPLACE is the digital eraser: match the OCR junk pattern and swap in clean text so geocoding sees a usable address. Counting matches or measuring edit distance does not scrub the line.
Full Explanation
Permits staff scrubbing OCR noise from address lines before geocoding need a transform that matches a noise pattern and replaces it with clean text (or empty). REGEXP_REPLACE applies that pattern-driven substitution so geocoders receive usable municipal addresses instead of OCR artifacts such as stray pipes, doubled spaces, or misread punctuation.
REGEXP_COUNT only tallies how many times a pattern occurs; counting noise tokens does not remove them. OPENJSON shreds JSON; it does not clean plain address text that is not stored as JSON. EDIT_DISTANCE measures how different two strings are; it does not apply a replacement pattern to scrub OCR junk from an address line.
Exam caveat: test replacements on a sample of real OCR failures—over-broad patterns can delete legitimate apartment or unit designators. Operational check: take an address with known OCR noise, apply REGEXP_REPLACE, geocode both raw and cleaned forms, and confirm only the cleaned form resolves to the correct parcel or rooftop. Chain replacements carefully—apply OCR artifact cleanup before punctuation normalization—and keep the original OCR text in an audit column when assessors must explain geocode overrides. Version the regex set with the permits app release so geocoding quality regressions can be traced to a specific scrubbing change.