Join using a LIKE clause is taking too long

Question

Please see the TSQL below:

create table #IDs (id varchar(100))
insert into #IDs values ('123')
insert into #IDs values ('456')
insert into #IDs values ('789')
insert into #IDs values ('1010')

create table #Notes (Note varchar(500))
insert into #Notes values ('Here is a note for 123')
insert into #Notes values ('A note for 789 here')
insert into #Notes values ('456 has a note here')

I want to find all the IDs that are referenced in the #Notes table. This works:

select #IDs.id from #IDs inner join #Notes on #Notes.note like '%' + #IDs.id + '%'

However, there are hundreds of thousands of records in both tables and the query does not complete. I was thinking about FreeText searching, but I don't think it can be applied here. A cursor takes too long to run as well (I think it will take over one month). Is there anything else I can try? I am using SQL Server 2019.

Answer 1

The size of the input is only one aspect of the solution.
By splitting the text to tokens you indeed increase the number of records, but in the same time you enable equality join, which can be implemented using Hash Join.
You should get the query results in a few minutes top, basically the time it takes to your system to do a full scan on both tables, plus some processing time.
No need for temp tables.
No need for indexes.

Select id
from #IDS 
where id in (select w.value 
             from #Notes as n
             cross apply string_split(n.Note, ' ') as w
            ) 

Fiddle

Per the OP request -
Here is a code that handles more complicated scenario, where an id could contain various characters (as defined by @token_char) and the separators are potentially all other characters

declare @token_char varchar(100) = 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'
;
with cte_notes as
(
    select  Note
           ,replace(translate(Note,@token_char,space(len(@token_char))),' ','') as non_token_char
                
    from    #Notes
)
select  id
from    #IDS 
where   id in
        (
            select  w.value 
            from    cte_notes as n
                    cross apply string_split(translate(n.Note,n.non_token_char,space(len(n.non_token_char))),' ') as w
            where   w.value != ''
        )
                                    

The Fiddle data sample was altered accordingly, to reflect the change

Answer 2

If you are going to do this search often you may want to explore using a wonderful (if underused) feature of SQL Server called 'Full Text Search.' To quote Microsoft:

A LIKE query against millions of rows of text data can take minutes to return; whereas a full-text query can take only seconds or less against the same data, depending on the number of rows that are returned.' I have seen searches go from minutes to seconds using this feature.

You would need to create a Full Text Search Catalog and then create indexs on the tables you want to search. It's not hard and will take you a few minutes to learn how to do this.

This is a good starting point:

https://docs.microsoft.com/en-us/sql/relational-databases/search/get-started-with-full-text-search?view=sql-server-ver15

Answer 3

I would apply CTE with string_split to filter out all alphabetic components and then join #ID table with the result of the CTE by id column. The query was tested on a sample of 1 mm rows.

With CTE As (
    Select T.value As id
    From #Notes Cross Apply String_Split(Note,' ') As T
    Where Try_Convert(Int, T.value) Is Not Null
)
Select I.id 
From #IDs As I Inner Join CTE On (I.id=CTE.id)

If you just want to extract a numeric value from a string, in this case join is excessive.

Select T.value As id, #Notes.Note 
From #Notes Cross Apply String_Split(Note,' ') As T
Where Try_Convert(Int, T.value) Is Not Null And T.value Like '%[0-9]%'

id	Note
123	Here is a note for 123
789	A note for 789 here
456	456 has a note here

No matter what, under the given circumstances, I would use join to filter out those numbers that are not represented in #IDs table.

With CTE As (
    Select distinct(id) As id
    From #IDs
)
Select T.value As id, #Notes.Note 
From #Notes Cross Apply String_Split(Note,' ') As T
            Inner Join CTE On (T.value=CTE.id)
Where Try_Convert(Int, T.value) Is Not Null 
      And T.value Like '%[0-9]%'

If the string contains brackets or parenthesis instead of spaces like this: "456(this is an id number) has a note here" or "456[01/01/2022]" as last resorts (since it degrades performance) you can use TRANSLATE to replace those brackets with spaces as follows:

With CTE As (
    Select distinct(id) As id
    From #IDs
)
Select T.value As id, #Notes.Note 
From #Notes Cross Apply String_Split(TRANSLATE(Note,'[]()','    '),' ') As T
            Inner Join CTE On (T.value=CTE.id)
Where Try_Convert(Int, T.value) Is Not Null 
      And T.value Like '%[0-9]%'

db<>fiddle