mysql - 使用Rlike连接两个字符串

我正在将表Table_A和Table_B连接到列Col_A和Col_B上。以下是一些测试样本值。

Table_A, Col_A
USA1FullCover
USAMainland
USA2Islands

Table_B, Col_B
USA
USA1
USA2

加入时，我需要将值“ USA”与一个数字精确匹配。例如，联接结果应如下所示。

Col_A          Col_B
USA1FullCover  USA1
USAMainland    USA
USA2Island     USA2

我正在尝试在MySQL中实现这一目标。我尝试了rlike函数。但是问题在于，使用rlike，我无法完全匹配它们。

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

我的问题是我该如何修复rlike，以使第一种情况不会发生，即RHS上没有数字时它不匹配？还是可以使用regex？使用LHS的子字符串无济于事，因为我们无法真正预先定义长度。

最佳答案

采用

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

匹配美国，后跟正好一位数字，再跟其他任何字符。

仅在存在这种模式时加入

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