SQL: how to query elements from one table that are not in another when the syntax is not the same?

Question

I have a question because I'm really bad at SQL. I understand the basic functions, but when it gets a little more complicated, I'm completely lost.

here's what i have:
tables: tA, tB
columns: tA: refA tB: refB
basically refA and refB are the same thing (some form identifier like xxx-xxx-xxx), but refB may contain information (e.g. xxx-xxx-xxx_Zxxx or xxx-xxx-xxx Zxxx)

here is what i know how to do this:
querying for elements that are in the table but not in the other (when they are exactly the same)

select refA
from tA
where not exists (select *
from tB
where tB.refB = tA.refA
)

:
, refA, refB. , , "" , , , - . :

SELECT refA
FROM tA
WHERE NOT EXISTS (SELECT * 
FROM tB
WHERE tB.refB LIKE CONCAT(tA.refA,'%'))

... , .

- , , , , ?
!

:
left() - , ref , ( ).
id - , , .

edit 2: , (MON, 10 )

, , :/

tA:
B20-60-04-6A-1
B20-60-04-6A-11
B20-60-04-6A-12
B20-60-04-6A-13

tB:
B20-60-04-6A-11_XX
B20-60-04-6A-12_XX
B20-60-04-6A-13_XX

mid(), left() .. , "B20-60-04-6A-1" (14 ) 14 , 3 , tB...

, ?

tA :
(X, XYZ: charaters. X: -)
Xxx---
Xxx---
Xxx----
Xxx-----
..

tB:
Xxx-xx-xx-xx-xx-XYZ-xx Z xxx_XX
Xxx-----XYZZxxx_XX
Xxx-xx-xx-xx-xx Z xxx_XX

XYZ 3 . XYZ, .

, , :
- -XYZ
- , no -XYZ , "" _"

, VBA, SQL... , , : D

Answer 1

, -, , refB, , refA. , - :

Left(tb.RefB, InStr(Replace(tb.RefB+"_", " ", "_"), "_") -1)

refB, "123-456 123 EXTRA STUFF" "123-456_123_EXTRA_STUFF" "123-456". refA.

EDIT: . :

• refB, ( , refB refA, , "123" "123 _" )
• refB ( Replace). , , 1 , .
• ( InStr). , refB refA .
• , .. .

, - :

select refA
from tA
where not exists (select *
from tB
where Left(tb.RefB, InStr(Replace(tb.RefB+"_", " ", "_"), "_") -1) = tA.refA
)

refB refA - :

refA
====
123
123-456
123-456-789

refB
====
123-456-789_This_is_a_test

refA refB refA, "123 *", "123-456 *" "123-456-789 *" "123-456- 789_This_is_a_test".

Answer 2

, left(), ? :

SELECT refA
FROM tA
WHERE NOT EXISTS (SELECT * 
FROM tB
WHERE Left(tB.refB, Len(tA.refA)) = tA.refA)

, , refA, :

SELECT refA
FROM tA
WHERE NOT EXISTS (SELECT * 
FROM tB
WHERE Left(tB.refB, Max(Instr(tA.refA, ' '), Instr(tA.refA, '_'))) = tA.refA)

Answer 3

, , A, B, B- ?

select refA
from tA
left outer join tB 
    on tA.refA = left( tB.refB, len(tA.refA)) --trim B id to the length of A's
where tB.refB is null

Answer 4

, , , wild card Access is *, ANSI 92, ALIKE % .

:

SELECT tA.refA
FROM tA
WHERE (((tA.refA) 
   Not In (SELECT Mid(tb.RefB,1,Len(ta.RefA)) FROM tb)));

Answer 5

. , , - ; , NULL.

/ , .

, , , , .

Answer 6

, , , :

SELECT refA 
  FROM tA
 WHERE NOT EXISTS (
                   SELECT *
                     FROM tB
                    WHERE tB.refB ALIKE tA.refA & '%'
                  );