Inner Join

Question

Inner Join

mtz676

SSCrazy Eights

Points: 9214
More actions
June 6, 2016 at 11:32 pm

#326722

How do I inner join between 2 tables(inner join TableA.ColA=TableB.ColA) with this kind of data ?
My comparision values from TableA will be values after the '-' i.e (SS,SE,SD,SA)
TableA TableB
ColA ColBColA ColB
ABCD-SS 2342 SS574
DD-SE 52555SE7748568
GHJKL-SD 6346SS7568
U-SA 747SD8678
Thanks
Use LIKE: TableA.ColA LIKE '%' + TableB.ColA
Use Right (if it's always the same number of characters: RIGHT(TableA.ColA, 2) = TableB.ColA
Use SUBSTRING/CHARINDEX (if the number of characters after the '-' is variable and there is only one '-' in the string: SUBSTRING(TableA.ColA, CHARINDEX('-', TableA.ColA), LEN(ColA)) = TableB.ColA
Use RIGHT/CHARINDEX/REVERSE if the number of characters after the '-' is variable and there are more than one '-' in the string: SELECT CHARINDEX('-', 'ABC-DS'), RIGHT(TableA.ColA, CHARINDEX('-', REVERSE(TableA.ColA)) - 1) = TableB.ColA
Create a computed column using one of the above methods and create an index on that computed column, plus any other appropriate columns.

Only the last option has any hope of giving you good performance.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

drew.allen SSC Guru Points: 76989 More actions · Answer 1

sqlnewbie17 (6/6/2016)
How do I inner join between 2 tables(inner join TableA.ColA=TableB.ColA) with this kind of data ?
My comparision values from TableA will be values after the '-' i.e (SS,SE,SD,SA)
TableA TableB
ColA ColBColA ColB
ABCD-SS 2342 SS574
DD-SE 52555SE7748568
GHJKL-SD 6346SS7568
U-SA 747SD8678
Thanks

You have a couple of choices, but most of them are going to perform horribly, because of the bad design. TableA.ColA should really be split into two columns.

mtz676 SSCrazy Eights Points: 9214 More actions · Answer 2

Use SUBSTRING/CHARINDEX (if the number of characters after the '-' is variable and there is only one '-' in the string: SUBSTRING(TableA.ColA, CHARINDEX('-', TableA.ColA), LEN(ColA)) = TableB.ColA

The above condition does not work.It returns nothing when I use it in a join.

When I run that on only TableA.ColA

it gives this error:

The multi-part identifier "TableA.ColA" could not be bound.

mtz676 SSCrazy Eights Points: 9214 More actions · Answer 3

I tried this code on one of the tables which has col values as fadsf-ff,fsdf-ffg,edf-d

and this code should select values after the '-' in the string from colA

SELECT SUBSTRING ([DB].[dbo].[Table1].[ColA],

CHARINDEX('_', [DB].[dbo].[Table1].[ColA]) + 1,

LEN([DB].[dbo].[Table1].[ColA]))

Its throwing a multi-part identifier error:

The multi-part identifier "[DB].[dbo].[Table1].[ColA]" could not be bound

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 4

Specify an alias for Table1 in your FROM clause and use that in your SELECT list.

John

drew.allen SSC Guru Points: 76989 More actions · Answer 5

sqlnewbie17 (6/8/2016)
I tried this code on one of the tables which has col values as fadsf-ff,fsdf-ffg,edf-d
and this code should select values after the '-' in the string from colA
SELECT SUBSTRING ([DB].[dbo].[Table1].[ColA],
CHARINDEX('_', [DB].[dbo].[Table1].[ColA]) + 1,
LEN([DB].[dbo].[Table1].[ColA]))
Its throwing a multi-part identifier error:
The multi-part identifier "[DB].[dbo].[Table1].[ColA]" could not be bound

Have you specified an alias for the table? If so, then you should use the alias.

Is your database named DB?

Is your table named Table1?

Is your column named ColA?

Also, your code is using an underscore '_', but the string is showing a hyphen '-'. You need to search for the hyphen, not the underscore.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA