Solution for the "LIKE vs. ?" Puzzle

re: Solution for the "LIKE vs. ?" Puzzle

Jason Massie — Wed, 01 Oct 2008 21:18:27 GMT

Gordon is a tsql madman. We tried but failed to get him to name is son Harry Klundt.

re: Solution for the "LIKE vs. ?" Puzzle

Adam Machanic — Wed, 01 Oct 2008 21:33:42 GMT

I should probably delete your post to keep a sense of decorum about this blog, but ... that was a much-needed bit of humor after a tremendously boring day, so it stays. Thanks for the comment!

re: Solution for the "LIKE vs. ?" Puzzle

Jonathan Kehayias — Wed, 01 Oct 2008 23:52:02 GMT

Can you recheck this:

SELECT *

FROM b1

JOIN b2 ON

'#'+b1.blat1+'#' = '#'+LEFT(b2.blat2,7)+'#'

That doesn't pass the test when I run it. Is there a typo in it perhaps? I only get 2 rows back from running that.

re: Solution for the "LIKE vs. ?" Puzzle

Adam Machanic — Thu, 02 Oct 2008 01:34:30 GMT

Hi Jonathan,

Sorry, I left it coded for 7 characters which was the example Kevan sent me. Change the 7 to a 5 and it should work. Or, rebuild b1 with blat1 typed as CHAR(7) and change the insert to do LEFT(AddressLine1, 7), and then leave the 7 and you'll see the same result as if you'd run b2.blat2 LIKE b1.blat1 + '%'. Making the same change to the base table and using the LEFT(b2.blat2,7) = b1.blat1 predicate will in fact NOT return the same exact results.

I hope that clarifies things?

re: Solution for the "LIKE vs. ?" Puzzle

Bruce M — Thu, 02 Oct 2008 17:49:10 GMT

Thanks for the answers! Two questions:

1. What is the joke? I didn't get it :(

2. Can you please explain the trick of using delimiters "#" for the input strings?

re: Solution for the "LIKE vs. ?" Puzzle

Adam Machanic — Thu, 02 Oct 2008 20:40:14 GMT

Hi Bruce,

The fact that you didn't get the joke is probably a good thing. Hint: if you want to get it, just put your mind firmly in the gutter. It's really quite sophomoric (but after my day yesterday, it was just what I needed).

The delimiters work because the data is CHAR(n), and if the input data length is < N, it will end up with trailing spaces. Those trailing spaces will end up being ignored with an equality predicate, but with LIKE, when you append the '%' they are preserved. So you end up getting different results for the equality (more rows returned) vs the LIKE predicate. Delimiting the data makes those rows not matched, because just as when adding the '%' those trailing spaces are preserved. I guess, in retrospect, you don't need to delimit the front of the string too, but arguably it's nice for consistency.

re: Solution for the "LIKE vs. ?" Puzzle

Bruce M — Sat, 04 Oct 2008 19:30:49 GMT

Oooh! Now I get it...initially, I thought it had something to do with t-sql.

Thanks for the explanation of the delimiters. Isn't that the same as using LTRIM? Or is using a function in a join slow down performance?

re: Solution for the "LIKE vs. ?" Puzzle

Brian — Wed, 19 Nov 2008 00:38:37 GMT

Hi Adam,

I don't imagine such an elegant solution is possible for the Like VS ? puzzle if the original question would have been:

SELECT *

FROM b1JOIN b2

ON b2.blat2

LIKE '%' + b1.blat1 + '%'

Nonetheless, I found this blog post very interesting, but you have an idea let us know.

Thanks.

re: Solution for the "LIKE vs. ?" Puzzle

Adam Machanic — Mon, 24 Nov 2008 22:02:03 GMT

Hi Brian,

Sure there is--it's called Full Text Search :-)