MySQL正则表达式查询：匹配被非字母数字字符包围的两个单词

Hey there! Let's break down your problem and fix this properly. First, let's restate your tables clearly so we're on the same page:

Table 1 (table1) #

Table 2 (table2) #

Your original query has a syntax error (missing a comma in concat('%',a,'%',b'%')), and more importantly, the LIKE pattern is way too loose—it'll match any string that has a.A and a.B anywhere, regardless of what's between them or around them.

Since you need to enforce that:

• The start of the string (before A's value) can only have non-alphanumeric characters (or nothing)
• Between A's value and B's value there are only non-alphanumeric characters (at least one, since they're two separate words)
• The end of the string (after B's value) can only have non-alphanumeric characters (or nothing)

We'll use regular expressions instead of LIKE, since they let us define precise patterns. Here's how to do it in common SQL databases:

MySQL / MariaDB

SELECT *
FROM table1 a
JOIN table2 b ON b.C REGEXP CONCAT(
  '^[^a-zA-Z0-9]*',  -- Start: 0+ non-alphanumeric chars
  a.A,               -- Exact match for A's value
  '[^a-zA-Z0-9]+',   -- Middle: 1+ non-alphanumeric chars (separates A and B)
  a.B,               -- Exact match for B's value
  '[^a-zA-Z0-9]*$'   -- End: 0+ non-alphanumeric chars
);

PostgreSQL

SELECT *
FROM table1 a
JOIN table2 b ON b.C ~ CONCAT(
  '^[^a-zA-Z0-9]*',
  a.A,
  '[^a-zA-Z0-9]+',
  a.B,
  '[^a-zA-Z0-9]*$'
);

Oracle

SELECT *
FROM table1 a
JOIN table2 b ON REGEXP_LIKE(b.C, CONCAT(
  '^[^a-zA-Z0-9]*',
  a.A,
  '[^a-zA-Z0-9]+',
  a.B,
  '[^a-zA-Z0-9]*$'
));

Let's break down the regex pattern for you (since you're new to regex):

• ^: Anchors the match to the start of the string (so we don't match a random substring in the middle)
• [^a-zA-Z0-9]*: Matches zero or more characters that are NOT letters or numbers (the [^...] is a negated character class)
• a.A: Matches the exact value from column A of table1
• [^a-zA-Z0-9]+: Matches one or more non-alphanumeric characters (the + ensures there's at least something separating A and B—no direct concatenation like ganesh)
• a.B: Matches the exact value from column B of table1
• [^a-zA-Z0-9]*$: Matches zero or more non-alphanumeric characters, anchored to the end of the string ($)

With your sample data, this query will return:

The row !!dhin!!esh won't match because dhin doesn't exactly match dhi from table1.

Important Note #

If your A or B columns ever contain regex special characters (like ., *, +, ?, etc.), you'll need to escape those characters first so they're treated as literal text. For example, in MySQL, you can use REGEXP_REPLACE to escape them:

SELECT *
FROM table1 a
JOIN table2 b ON b.C REGEXP CONCAT(
  '^[^a-zA-Z0-9]*',
  REGEXP_REPLACE(a.A, '([.[\]{}()*+?^$\\|-])', '\\1'),
  '[^a-zA-Z0-9]+',
  REGEXP_REPLACE(a.B, '([.[\]{}()*+?^$\\|-])', '\\1'),
  '[^a-zA-Z0-9]*$'
);

内容的提问来源于stack exchange，提问作者Ganesh selvam