October 8, 2026 at 5:27 pm
Title pretty much says it. I want to join to a table that has entries like Sand and Sandstone, or Chert and Chert with striations. The table I am join to has these expressions as the leading text in a longer text field, so I am joining with trailing wildcards. If my table has the longer entry, I want it to join on that, but if it has the shorter variation, I still want it to join. Is there a way to tell SQL Server to join on the longest possible match?
October 8, 2026 at 7:30 pm
Yes. Use a CROSS APPLY or OUTER APPLY (rather than a JOIN) with a TOP (1) ORDER BY LEN(<matched_column>) DESC
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 8, 2026 at 7:32 pm
For example,
SELECT t1...., ca1....
FROM dbo.table1 t1
CROSS APPLY (
SELECT TOP (1) ...
FROM dbo.table2 t2
WHERE t2.column LIKE t1.column + '%'
ORDER BY LEN(t2.column) DESC
) AS ca1
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 8, 2026 at 8:33 pm
For example,
SELECT t1...., ca1....FROM dbo.table1 t1CROSS APPLY ( SELECT TOP (1) ... FROM dbo.table2 t2 WHERE t2.column LIKE t1.column + '%' ORDER BY LEN(t2.column) DESC) AS ca1
Yes, that looks like exactly what I'm after. Thank you - I'm just starting to experiment with Cross Apply, but I'm pretty much at the starting gate with it. This will certainly help.
October 9, 2026 at 2:34 pm
For example,
SELECT t1...., ca1....FROM dbo.table1 t1CROSS APPLY ( SELECT TOP (1) ... FROM dbo.table2 t2 WHERE t2.column LIKE t1.column + '%' ORDER BY LEN(t2.column) DESC) AS ca1
Just tried it, works great. Exactly what I needed - thank you again.
October 11, 2026 at 3:13 am
You're welcome! Very glad it helped. Thanks for the nice feedback.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply