Joining two tables based on substring from foreign keys (MS SQL2005)

Question

I am stuck in joining two tables "a" and "b". The structure of these two tables is shown below.

Table a

+-------------+---------------------+
+ SKU         |     Title           +
+-------------+---------------------+
+ 12345_786   |  Some text          +
+             |                     +
+ 12345_231   |  Sony               +
+             |                     +
+ 12345_222   |  Samsung            +
+             |                     +
+ 67481_21    |  IBM                +
+             |                     +
+ 88723_231   |  HP                 +
+-------------+---------------------+

Table B

+-------+---------------------+
+ SKU   |     Price           +
+-------+---------------------+
+ 786   |  $230               +
+       |                     +
+ 231   |  $540               +
+       |                     +
+ 222   |  $120               +
+       |                     +
+ 21    |  $220               +
+       |                     +
+ 231   |  $50                +
+-------+---------------------+

The SKU table is ParentSKU + "+ Child SKU. Thus, each sku in the table has a child sku. The parent child is smoothed by the letter" ".

I want to join table A.SKU after "_" in table B.SKU

So far I have tried the following query, but without the desired result.

SELECT A.SKU,B.Price
FROM A
INNER JOIN
B ON 
Substring(A.SKU, patindex('%_%', A.SKU), 
Cast(Len(A.SKU) as int)-cast(patindex('%_%',A.SKU)as int)) 
= CAST(B.SKU AS varchar(12))

Your help in this context will be greatly appreciated.

Answer 1

, , , _ , % .

, PATINDEX?

Answer 2

"_", OR . .

( ) . , , .

Answer 3

SUBSTRING, , :

select *
from #t1
inner join #t2
on substring(#t1.sku, charindex('_', #t1.sku)+1, 3) = #t2.sku

substring(#t1.sku, charindex('_', #t1.sku)+1, 3) , .

Answer 4

A:

• SKU1 ( 12345, 67481 ..)
• SKU2 ( 786, 231 ..)
• SKU computed ( ) SKU1 + '_' + SKU2.

, TableA.SKU2 TableB.SKU.

:

SELECT A.SKU,B.Price
FROM
    TableA A
    INNER JOIN TableB B ON A.SKU2 = B.SKU

:

• JOIN , SKU2.

Answer 5

mondayUK , .

, , , , - : -

SELECT A.SKU,B.Price
FROM A
INNER JOIN B 
ON CAST(SUBSTRING_INDEX(A.SKU, '_', -1) AS UNSIGNED) = B.SKU

EDIT - MySQL,

Answer 6

, ?

select a.sku, b.price
from a 
inner join b
on a.sku LIKE '%_' + b.sku