Q:

Joining two strings using rlike

I am joining the tables Table_A and Table_B on columns Col_A and Col_B. Below are some test sample values. 
Table_A, Col_A
USA1FullCover
USAMainland
USA2Islands

Table_B, Col_B
USA
USA1
USA2

When joining, I need to match the value 'USA' followed by a number exactly.  For instance, the join result should look like this. 
Col_A          Col_B
USA1FullCover  USA1
USAMainland    USA
USA2Island     USA2

I'm trying to achieve this in MySQL.  I tried the rlike function.  But the issue is that with rlike, I am not able to completely match them. 
select
case when 'USA1FullCover' rlike 'USA' then 1 else 0 end;
#matches, but shouldn't

select
case when 'USA1FullCover' rlike 'USA1' then 1 else 0 end;
#matches, which is what I need/expect

select
case when 'USA1FullCover' rlike 'USA2' then 1 else 0 end;
#doesn't match, which is what I need/expect

My question is how can I fix the rlike so that the first case doesn't happen i.e. it doesn't match when there is no digit on the RHS?  Or is it possible using regex?  Taking a substring of the LHS doesn't help since we cannot really define the length beforehand. 

A:

Use 
col_A regexp '^USA[0-9]{1}.+$'

to match USA followed by exactly one digit followed by any other characters. 
To join only when such pattern exists 
select a.*,b.* 
from tableA a
join tableB b on 
case when a.col_A regexp '^USA[0-9]{1}.+$' then substring(a.col_A,1,4) else '' end
= b.col_B

