Why is it OK to ignore that 0? What if it was a different number? What if the two codes don’t match in a different position?
It is simple to write a query that matches these two rows, but without knowing the rule in your head, that query will not work for any other two rows with different values.
[QUOTE=Dan Grossman;4539648]That does not answer the question. Sorry Sir I thinking explicit my problem.
Why do you equate XX00138410 to XX00-1-380410? I equate XX00138410 to XX00-1-380410 because I need the value of field NAME, recorded in tableB and not in tableA. I working only values of fields the tableA, but this field NAME not existing in tableA and not know how to recover…
Why is it OK to ignore that 0? What if it was a different number? What if the two codes don’t match in a different position? The 0 value (and “-” symbol) in the string is always in the same location
SELECT a.id
, a.code
, b.name
FROM TableA AS a
INNER
JOIN TableB AS b
ON b.code = CONCAT(SUBSTRING(a.code FROM 1 FOR 4)
,'-'
,SUBSTRING(a.code FROM 5 FOR 1)
,'-'
,SUBSTRING(a.code FROM 6 FOR 2)
,'0'
,SUBSTRING(a.code FROM 8 FOR 3)
)