Replace using Locate and/.or regex?

问题:

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?

回答1:

Ok for now this is my non-regex solution:

Replace(FIELD,Mid(FIELD,Locate('License',FIELD)-3,2),'State') 
from Table where FIELD regexp '
[[:<:]](AL|AK|AZ|AR|...|WY)[[:>:]] License';

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:

Replace(FIELD,mid(FIELD,((Locate('License',FIELD)-1) –
 (CHAR_LENGTH(substring_index(left(FIELD,Locate('License',FIELD)-2),' ',-1))+1))+1,
((CHAR_LENGTH(substring_index(left(FIELD,Locate('License',FIELD)-2),' ',-1))+1))),'')
from TABLE where FIELD regexp '(Alabama|Alaska|Arizona|…|Wyoming)) License';