October 19, 2020 at 4:51 pm
Friends
Bit confused query for me. Dunno how to ask correctly.
I have two tables. One with only one column with DocumentNo. (around 6000 records)
Another table with many columns. One of those is with a long sentence which may contain one of those documentno from table1. This DocumentNo may appear anywhere in that text in Table2. (Table2 has 100s of 1000s of records)
How do I extract all records from Table2 that contains these document nos from Table1?
October 19, 2020 at 5:17 pm
SELECT T1.DocumentNo, T2.LongSentence
FROM Table2 T2
INNER JOIN Table1 T1 ON T2.LongSentence LIKE '%' + T1.DocumentNo + '%'
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
October 20, 2020 at 1:51 am
Thank you
Refreshed my mind and helped a lot
Appreciate it.
October 20, 2020 at 4:39 am
You will need to test, but using PATINDEX *might* give you slightly better performance.
SELECT T1.DocumentNo, T2.LongSentence
FROM Table2 T2
INNER JOIN Table1 T1 ON PATINDEX('%' + T1.DocumentNo + '%', T2.LongSentence) > 0
Note, that since you can't create an index to assist with a wildcard search, both of these queries are going to be slow.
October 20, 2020 at 2:19 pm
On the subject of "performance", trailing and mid-string lookups are terrible. If you have to do this more than once, consider another method especially if the data is relatively static. For example, FULL TEXT lookups or a homegrown version of that can provide a huge benefit. A homegrown version might simply pre-split the data and memorize the PK and the document numbers into a single "vertical" table (Name Value Pair (NVP) or Entity Attribute Value (EAV... big brother of NVP).
--Jeff Moden
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply