Inner join two tables on column1 like column2 followed by only non alphabets

Question

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

Answer 1

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;