Oracle hr schema, locations table, street_address column, string functions

Other Tech Topics
1

Greetings.

The task is to replace last letter for each row in STREET_ADDRESS column of LOCATIONS table with '!' sign. But you can use only string function such as LOWER, UPPER, INSTR, SUBSTR, REVERSE, REPLACE, TRANSLATE, TRIM, R\LPAD. Below is (select street_address from locations):

1297 Via Cola di Rie
93091 Calle della Testa
2017 Shinjuku-ku
9450 Kamiya-cho
2014 Jabberwocky Rd
2011 Interiors Blvd
2007 Zagora St
2004 Charade Rd
147 Spadina Ave
6092 Boxwood St
40-5-12 Laogianggen
1298 Vileparle (E)
12-98 Victoria Street
198 Clementi North
8204 Arthur St
Magdalen Centre, The Oxford Science Park
9702 Chester Road
Schwanthalerstr. 7031
Rua Frei Caneca 1360
20 Rue des Corps-Saints
Murtenstrasse 921
Pieter Breughelstraat 837
Mariano Escobedo 9991

Some rows have digits, space or sign at their end. Need to avoid them and replace last letter that comes before them to be replaced with '!'

For example, last row is:
Mariano Escobedo 9991

Should be ↓
Mariano Escobed! 9991

Kindly ask you to inform me if this task with these functions restrictions can not be solved.

Thanks in advance.

2

If you aren't allowed to use REGEXP_REPLACE...

Then you'll need to use a series of functions to:
Reverse the string.
Find the location of the first character matching the pattern you wish to replace ([a-z]).
Find the part of the string before and after that character's location, and combine them together with the ! in between them. (Or use whatever equivalent of STUFF exists in Oracle)
Reverse the string.