LIKE operator with INNER JOIN

Question

Post reply

LIKE operator with INNER JOIN

Deepak.Sharma507

Ten Centuries

Points: 1225
More actions
November 1, 2012 at 9:20 am

#269454

Hi all,
I have to join two tables with the common field being the [SONumber]
Well the problem I am facing is SONumber in table[Table1] which I am joining contains the value "123" and the value on the other table[Table2] is "123/1", "123/2"
I want to return all the values where SONumber is like '123%'
I tried
SELECT Table1.Col1, Table1.Col2, Table1.Col3, Table2.Col1 FROM Table2
LEFT OUTER JOIN Table1 ON Table1.SONumber like '%' + Table2.SONumber + '%' WHERE ...
But its not giving me desired results.
Any help will be greatly appreciated.
Regards,
Deepak:-)

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 1

Try swapping Table1.SONumber and Table2.SONumber

Table2.SONumber like '%' + Table1.SONumber + '%'

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Deepak.Sharma507 Ten Centuries Points: 1225 More actions · Answer 2

Hi Mark,

Thanks for your reply. I have already tried that with no luck... 🙁

Cheers,

Deepak

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

This is a bit unorthodox but I think it would work in your case. Given that you can't expect great performance out of this due to the strange way you need to find your data I think this will work.

create table #Table1

(

SONumber varchar(10)

)

create table #Table2

(

SONumber varchar(10)

)

insert #Table1

select '123'

insert #Table2

select '123/1'

select *

from #Table1 t1

left join #Table2 t2 on CHARINDEX(t1.SONumber, t2.SONumber) > 0

drop table #Table1

drop table #Table2

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

mickyT SSChampion Points: 10360 More actions · Answer 4

Assuming that your data is always in the format that you have indicated, you could try

create table #Table1

(

SONumber varchar(10)

)

create table #Table2

(

SONumber varchar(10)

)

insert #Table1

select '123'

insert #Table2

select '123/1'

select *

from #Table1 t1

left join #Table2 t2 on t1.SONumber = SUBSTRING(t2.SONumber, 1, len(t1.SONumber))

drop table #Table1

drop table #Table2

Sticking with the like option this should work as well

select *

from #Table1 t1

left join #Table2 t2 on t2.SONumber like t1.SONumber + '%'

Luis Cazares SSC Guru Points: 183706 More actions · Answer 5

Micky,

Your first query is not sargable and that would make it pretty inefficient compared to the second one. 😉

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

mickyT SSChampion Points: 10360 More actions · Answer 6

Luis Cazares (11/1/2012)
Micky,
Your first query is not sargable and that would make it pretty inefficient compared to the second one. 😉

True :Whistling:

Not knowing what the data is for, I suspect a better option would be to split the SONumber in table 2 to SONumber and Suffix, then there would be no reason for the Like join

Luis Cazares SSC Guru Points: 183706 More actions · Answer 7

mickyT (11/1/2012)
Luis Cazares (11/1/2012)
Micky,
Your first query is not sargable and that would make it pretty inefficient compared to the second one. 😉
True :Whistling:
Not knowing what the data is for, I suspect a better option would be to split the SONumber in table 2 to SONumber and Suffix, then there would be no reason for the Like join

True as well.

Most problems in T-SQL can be prevented or corrected with a good DB design.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

vinu512 SSCoach Points: 15729 More actions · Answer 8

This would also work fine for your requirement:

Create Table Ex

(Id Int Identity(1,1),

Value NVarchar(5) )

Create Table Ex1

(Id Int Identity(1,1),

Value NVarchar(5) )

Insert Into Ex

Select '123'

Insert Into Ex1

Select '123/1'

Select * From Ex As a JOIN Ex1 As b ON b.Value LIKE Left(a.Value, CHARINDEX('/', a.Value)) + '%'

Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

Deepak.Sharma507 Ten Centuries Points: 1225 More actions · Answer 9

Hi all,

Thanks for all your replies and time.

It seems the data in my table is in really bad shape. Though the solutions provided by you all is correct but somehow is not getting me the desired results. I created a test environment with some test data and it worked fine, but not with the Live data.

I'll therefore try a whole different approach for this now.

Thanks again for your time.

Cheers,

Deepak