Wild Join Question

  • Carleton

    SSCommitted

    Points: 1706

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

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    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: 12292

    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

    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: 3383

    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

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

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