Nested statements using Update

[Rose427]

I am using the following statement to replace address abbreviations. I would like to expand this to look at multiple abbreviations which would be replace with the same word (ex: ST, ST., STR would all be replace with STREET). Right now I have indivisual statements for each abbreviation.

UPDATE Address SET Address.PHYSICAL_ADDRESS = Replace([PHYSICAL_ADDRESS]," ST "," STREET ")
WHERE (((Address.[PHYSICAL_ADDRESS]) Like "* ST*"));

[Rabbit]

You just need to nest your replace functions in each other.

Replace(Replace(someString, abbrev1, replace1), abbrev2, replace2)

[Rose427]

Should I also nest the Where statement?

[Rabbit]

You can't nest a where statement; it's not a function. Use an OR to combine the two filters.

WHERE 1 OR 2

[Rose427]

I updated the query to:

UPDATE Addresses SET Addresses.PHYSICAL_ADDRESS = Replace(Replace([PHYSICAL_ADDRESS], " DR", " DRIVE"), " DRV", " DRIVE")
WHERE ((Addresses.[Physical_Address] Like "* DR*" or  Like  "* DRV*"));

I am getting a Syntax error on the "Where" statement saying that I am missing an operator. I thought "Like" was the operator.

[Rabbit]

Please use code tags when posting code.

You're correct, Like is an operator. That means you need to give it two operands. You only have one operand in the second like.

Addresses.[Physical_Address] Like "* DR*" or Addresses.[Physical_Address] Like "* DRV*"

On a side note, I'm not sure you want to replace " st" because "123 stanton st" becomes "123 streetanton street". The same applies to your dr.

[Rose427]

Thank you, it is working now. I agree with you regarding " ST" and " DR". Not sure how I am going to handle that yet. It would be easy if I could guarantee that there was a space after, but I know there 8 times out of 10 there is not.

[Rabbit]

Well if there's no space after it, what's after it? A period I assume. Just use two replacements, one for " st " and one for " st. "

[Rose427]

The "st" is usually at the end of the line so it it not followed by any characters.

[Rose427]

There are also many street names that start with ST, like St Mary Ave or even St John St without a period at the end of either. Do you think the use of "Last" would be useful in this situation?

[Rabbit]

If it's not followed by anything, you can append a space to the end yourself. There's not much you can do about st mary st unless you were willing to learn and use regular expressions. I don't know what you mean by last, I'm not aware of any such function. If you mean replace just the last occurrence, you could do that.