Extract rows with different string

[cms9651]

Extract rows with different string

Hello people, this is my first post... I hope your help

The Db is MySQL 5.0.45-community-nt via TCP/IP

TableA:

ID	CODE
1	XX00138410

TableB:

ID	CODE
18	XX00-1-380410

If I execute this query:

SELECT
A.CODE, B.CODE
FROM TableA A
INNER JOIN TableB B ON
A.CODE = REPLACE(B.CODE, "-","")

The result is 0 rows fetched, because the code of TableB is XX001380410:
in the code of TableB I have other zero: XX001380410

Can someone help me?
Thanks in advance.
Chevy

[Dan Grossman]

What is the rule by which you consider these rows a match?

You need to be able to clearly, unambiguously write that down before you can write a query which expresses it.

[cms9651]

Ok Sir, I will be more explicit:

TableA:

ID	CODE
1	XX00138410

TableB:

ID	CODE		NAME
18	XX00-1-380410	BECKAMS

I need this output:

A.ID	A.CODE		B.NAME
1	XX00138410	BECKAMS

Thanks in advance.
Chevy

[Dan Grossman]

That does not answer the question.

Why do you equate XX00138410 to XX00-1-380410?

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.

[cms9651]

[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

Thanks.
Chevy

[r937]

The 0 value (and "-" symbol) in the string is always in the same location

okay, then you're in business

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)
                      )

[cms9651]

okay, then you're in business

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)
                      )

Thanks for your suggestion.