I am trying to replace occurences of state abbreviations in a document when they occur before the word license (and no where else) with the word 'State'
I can find them with:
Select FieldA where regexp '(AL|AK|AZ|..|WI|WY) License'
Clearly I could replace 'License' if I want but I want to replace the state name in fact I want to get rid of it). I thought I could use Locate to find that position and use mid(
)` but regex inside locate is not working:
Select Locate((regexp '(AL|AK|AZ|..|WI|WY) License'),FieldA)
So is there some way to use Locate so I can essentially find the potion of the (always) 2-letter acronym occurring before 'License' and replace it with a different string?
Ok for now this is my non-regex solution:
For what it is worth even though somewhat non-apropos to the Regex specific solution to this question but solves it anyway, I also applied the same though a more complex solution to replace full states which was harder given their variable length: