Wild Join Question

Question

Wild Join Question

Carleton

SSCommitted

Points: 1706
More actions
December 23, 2009 at 10:14 pm

#215058

Comments posted to this topic are about the item Wild Join Question

Viewing 5 posts - 1 through 4 (of 4 total)

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

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 1

Carleton (12/23/2009)
If you are unsure if the field contains a wildcard character when joining using the LIKE operator, you can avoid false positive results by observing this tip:

But what is the point of using LIKE if you do this? Why not simply use the = operator?

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

sknox SSChampion Points: 12388 More actions · Answer 2

Hugo Kornelis (12/24/2009)
Carleton (12/23/2009)
If you are unsure if the field contains a wildcard character when joining using the LIKE operator, you can avoid false positive results by observing this tip:
But what is the point of using LIKE if you do this? Why not simply use the = operator?

In general, you probably wouldn't use LIKE exactly like that, but with your own wildcards attached. For example, you may want to see if a string contains another string in its entirety. If the second string might have a wildcard in it, you'd have to do something like:

select * from #table1 t1

inner join #table2 t2

on t1.Col1 like

'%' + REPLACE(REPLACE(t2.Col1,'%','[%]'),'_','[_]') + '%'

Otherwise, the wildcard in the second string may cause false positives.

Chris Carter-210078 SSC Rookie Points: 41 More actions · Answer 3

The complete answer should be “It depends”. If you have a case sensitive collation designator you will get:

Msg 208, Level 16, State 0, Line 3

Invalid object name '#table1'.

Since the script created a table #Table1 and tries to reference #table1

BudaCli Hall of Fame Points: 3404 More actions · Answer 4

Chris Carter-210078 (12/30/2009)
The complete answer should be “It depends”. If you have a case sensitive collation designator you will get:
Msg 208, Level 16, State 0, Line 3
Invalid object name '#table1'.
Since the script created a table #Table1 and tries to reference #table1

You're correct but based on what's given then you'll need to set collation to something that will provide with results for this scenario.

What you don't know won't hurt you but what you know will make you plan to know better

Wild Join Question

Cookies on SQLServerCentral