I have a unique scenario in where I need to use the regexp_replace function to accomodate what the client is looking for. Here is the scenario, they have field for an address that has ''1000 Jefferson's Hou'snty Parkway" and they want the 's' in Jefferson's to stay lower case but the 's' in Cou'snty should be upper case. So the regexp_replace function needs to look at the string '1000 Jefferson's Hou'snty Parkway' and understand that if the 's is at the very end then it needs to be lower case but the other one needs to be 'S.
Here is what I have so far but it only takes care of the Jefferson's scenario but not the Hou'snty word. Can you help me in writing the correct expression for this scenario?
select regexp_replace(ADDRESS_1, '''S$','''s')