英文:
Inner join two tables on column1 like column2 followed by only non alphabets
问题
我有两个表 - 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 |
英文:
I have two tables - 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 |
I want to join the two tables on a condition name-like pattern followed by only non-alphabets.
select
A.name, B.pattern
from
name_table A
inner join
pattern_table B on A.name like B.pattern +'[^a-zA-Z]%'
Here ssn is matched with ssn_12nam - I only want name followed by non-alphabets or name = pattern. pattern ss should not match with ssn, ssn_123, etc.
This is the output I am looking for :
| name | pattern |
|---|---|
| ssn_1 | ssn |
| ssn | ssn |
| ssn123 | ssn |
| ss123 | ss |
| ss_12 | ss |
答案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;
英文:
You can try joining on the following criteria:
select n.name, p.pattern
from name_table n
join pattern_table p on Replace(n.name, p.pattern, '') not like '%[A-z]%';
You might find it easier to implement the logic in a 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;
答案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
英文:
Try this. The idea is to find 100% pattern match first and then find others using mask but exclude results with letters.
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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论