How can I replace a word in SQL but only if it is the last word in the string for a scalar-valued function?

Question

I am trying to use a function to clean address data and want to replace the word "Street" with "St" but only in the cases where "Street" is the last word in the string. See the imageenter image description here

I tried using a WHERE clause like this: enter image description here

but i get a syntax error likely b/c this is not an actual column but part of the function i built.

Any help appreciated. thanks

I tried using a WHERE clause like this: enter image description here

but i get a syntax error likely b/c this is not an actual column but part of the function i built.

Any help appreciated. thanks