Need help. Find matchng Substring

Question

Post reply

Need help. Find matchng Substring

meelan

SSC Eights!

Points: 997
More actions
October 19, 2020 at 4:51 pm

#3798917

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?

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

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

ScottPletcher SSC Guru Points: 101120 More actions · Answer 1

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) "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".

meelan SSC Eights! Points: 997 More actions · Answer 2

Thank you

Refreshed my mind and helped a lot

Appreciate it.

DesNorton SSC-Insane Points: 24401 More actions · Answer 3

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.

How to post data/code on a forum to get the best help.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 4

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)