I am trying to homogenize and clean up address data. Here is the calculated field equation that I am currently working with.
TRIM(IF CONTAINS([Address Upper]," ROAD") THEN (REGEXP_REPLACE([Address Upper]," ROAD"," RD"))
ELSEIF CONTAINS([Address Upper]," STREET") THEN (REGEXP_REPLACE([Address Upper]," STREET"," ST"))
ELSEIF CONTAINS([Address Upper]," AVENUE") THEN (REGEXP_REPLACE([Address Upper]," AVENUE"," AVE"))
ELSEIF CONTAINS([Address Upper]," BOULEVARD") THEN (REGEXP_REPLACE([Address Upper]," BOULEVARD"," BLVD"))
ELSEIF CONTAINS([Address Upper]," DRIVE") THEN (REGEXP_REPLACE([Address Upper]," DRIVE"," DR"))
ELSEIF CONTAINS([Address Upper]," LANE") THEN (REGEXP_REPLACE([Address Upper]," LANE"," LN"))
ELSEIF CONTAINS([Address Upper]," CIRCLE") THEN (REGEXP_REPLACE([Address Upper]," CIRCLE"," CIR"))
ELSEIF CONTAINS([Address Upper]," TERRACE") THEN (REGEXP_REPLACE([Address Upper]," TERRACE"," TER"))
ELSEIF CONTAINS([Address Upper]," PLACE") THEN (REGEXP_REPLACE([Address Upper]," PLACE"," PL"))
ELSEIF CONTAINS([Address Upper],".") THEN (REGEXP_REPLACE([Address Upper],"."," "))
ELSEIF CONTAINS([Address Upper]," ") THEN (REGEXP_REPLACE([Address Upper]," "," "))
ELSEIF CONTAINS([Address Upper],CHAR(92)) THEN (REGEXP_REPLACE([Address Upper],CHAR(92)," "))
ELSE [Address Upper] END)
You can see the last expression is looking for the "\" and should be replacing it with a space. It is not working. I actually want to remove a "\n", which seems impervious to removal or being able to be "found" in the string. I can manually delete the characters which will merge it with the other similar values, however my data set has 8K lines and I don't really want to look through them all for this.
Thanks.
