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 the two tables is given 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                +
+-------+---------------------+

Table SKU convention is ParentSKU + "" + Child SKU. So each sku in table has a child sku. Parent child is saperated by "".

I wan to Join table A.SKU part after "_" on table B.SKU

So far I have tried the following query but without 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 highly appreciated.

@Mehboob I think this is also do-able without using `substring`, but instead use [`LIKE`](http://msdn.microsoft.com/en-us/library/ms179859.aspx) when joining the tables. — seph, Apr 17 '14 at 09:54

score 4 · Accepted Answer · edited May 23 '17 at 11:57

4

The problem you're finding is because the _ underscore character has a meaning in a pattern string, much like the % percent character.

You need to escape it as referenced in this post How to escape underscore character in PATINDEX pattern argument?

edited May 23 '17 at 11:57

Community

1
1

answered Apr 17 '14 at 09:32

mondayuk

64
2

Thanks a lot. The main problem was the underscore has meaning while using in pattern. – Mehboob Afridi Apr 17 '14 at 09:38
(+1) for **nice answer** and welcome to `stackoverflow`. – jmail Apr 17 '14 at 09:41

score 0 · Answer 2 · answered Apr 17 '14 at 09:29

0

I would either create a new column on the table with the value after "_" and then use this to join on OR create an indexed view. The view would create the new column.

Your select query could then join on this new column (from the table or view) without having to do any additional string manipulation. This may also prevent Search Arguments if you then have an index setup on this new column thereby keeping your query execution performant.

answered Apr 17 '14 at 09:29

sarin

5,227
3
34
63

I am working for one of my client and cannot change the database structure. – Mehboob Afridi Apr 17 '14 at 09:31

score 0 · Answer 3 · answered Apr 17 '14 at 09:33

You can join on SUBSTRING, but it wont perform very well, cause you cannot index it:

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

If you could create a computed column on substring(#t1.sku, charindex('_', #t1.sku)+1, 3), and an index on it, it would be better.

score 0 · Answer 4 · answered Apr 17 '14 at 09:41

I would reconsider the design of TableA:

Add a new column SKU1 (with values 12345, 67481, etc )
Add a new column SKU2 (with values 786, 231, etc)
Re-create SKU column as computed (eventually persisted) column as SKU1 + '_' + SKU2.

Also, I would consider to use the same data type for columns TableA.SKU2 and TableB.SKU.

Then simply rewrite your query:

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

The reasons for doing this:

review functional dependency
JOIN will benefit by adding an index on SKU2 column.

Kickstart · Answer 5 · 2014-04-17T12:40:13.890

0

The reply by mondayUK explains the issue you are having.

If you just want a solution requiring no changes to the tables to get the result you want then maybe something like this:-

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

EDIT - this is for MySQL, which this question is tagged with

edited Apr 17 '14 at 12:40

answered Apr 17 '14 at 09:46

Kickstart

21,403
2
21
33

score 0 · Answer 6 · answered Apr 17 '14 at 09:48

0

Wouldn't something like this work?

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

answered Apr 17 '14 at 09:48

seph

674
2
8
23

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

6 Answers6