Replace "&" with "and" in your data

Owned by Gavin Kelly, created
Last updated: 29 Sept, 2022 by Kim Stimpson
2 min read

Overview

In the AddressBase Premium data some of the Wards and District names contain ampersands (&) and this can result in information not displaying in the My Property tab. For example no councillors showing for the ward below.

Step-by-step guide

  • Locate the table(s) your layer(s) that contain ampersands

  • Locate the Workflow Job in Studio that populates these tables

  • Add a Stored Procedure Task into the Workflow Job. Place it in the list after the Tasks that populate the tables

  • Then select the Workflow radio button as this will limit the list of available Functions and then select the at_wkf_executecmd entry from the list (from v6.0.0 this is called wkf_executecmd under the -Workflow- Function filter)

  • Enter the following information into the Edit parameter box changing the table and field names as required

UPDATE boundaryline_wards SET name = REPLACE(name,'&','and') WHERE name like '%&%'

When run this task will replace any instances of '&' with 'and' in the name field of the boundaryline_wards table.