问题

我有两个表 - name_table：

|Id|name| data_type|
|----|-----------|--------------|
|1|ssn_1|int|
|2|ssn_12nam|varchar|
|3|ssn|int|
|4|ssn123|int|
|5|ssnnam|varchar|
|6|ss123|varchar|
|7|ss_12|int|
|8|ssnam|varchar|

pattern_table：

|Id        | pattern   |
|----------|-----------|
|10        |  ssn      |
|11        |   ss      |


我想根据条件 name-like pattern，并且只有后面跟着非字母字符的情况下连接这两个表。

select
A.name, B.pattern
from
name_table A
inner join
pattern_table B on A.name like B.pattern + ''[^a-zA-Z]%'';

这里 `ssn` 与 `ssn_12nam` 匹配 - 我只想要后面跟着非字母字符的名字，或者名字等于 pattern。pattern ss 不应该与 ssn、ssn_123 等匹配。

这是我期望的输出：

|name        |  pattern|
|------------|-------------|
|ssn_1       |    ssn      |
|ssn         |    ssn |
|ssn123      |    ssn  |   
|ss123       |    ss  |
|ss_12       |    ss   |

select 
    A.name, B.pattern 
from 
    name_table A 
inner join 
    pattern_table B on A.name like B.pattern +'[^a-zA-Z]%'

答案1

得分: 3

你可以尝试按照以下标准进行连接：

select n.name, p.pattern
from name_table n 
join pattern_table p on Replace(n.name, p.pattern, '') not like '%[A-z]%';

你可能会发现在cross apply中实现这个逻辑更容易：

select n.name, p.pattern
from name_table n 
cross apply (
  select pattern
  from pattern_table p
  where Replace(n.name, p.pattern, '') not like '%[A-z]%'
)p;

select n.name, p.pattern
from name_table n 
join pattern_table p on Replace(n.name, p.pattern, '') not like '%[A-z]%';

select n.name, p.pattern
from name_table n 
cross apply (
  select pattern
  from pattern_table p
  where Replace(n.name, p.pattern, '') not like '%[A-z]%'
)p;

答案2

得分: 2

select 
    A.name, B.pattern 
from 
    tmp A 
inner join 
    pat B on 1 = case 
                    when A.name = B.pattern then 1
                    when A.name like B.pattern+'[^a-zA-Z]%' and replace(A.name,B.pattern,'') not like '%[a-zA-Z]%' then 1
                 else
                    0
                 end

select 
    A.name, B.pattern 
from 
    tmp A 
inner join 
    pat B on 1 = case 
                    when A.name = B.pattern then 1
                    when A.name like B.pattern+'[^a-zA-Z]%' and replace(A.name,B.pattern,'') not like '%[a-zA-Z]%' then 1
                 else
                    0
                 end