The query processor ran out of internal resources | Workaround to use IN (tsql) with wildcards?

user_11729768 0
Viewed 1093

I need to find a large amount of values in my database.

Below is what the data may look like: 

0054321
54321
999
999  - HALLO?
54321 Hallo?
000054321

Since this is a one time thing, I thought I'd simply make a really long query searching through the data using OR and LIKE: 

... WHERE ' ' + FLD1 + ' ' LIKE '%54321 %' 
OR        ' ' + FLD1 + ' ' LIKE '%999 %' 
OR ...

Imagine this but with 80k OR. In this case, SQL Server will run into the following issue:

The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.

As a workaround, I read that you're supposed to declare a temporary TABLE and put your values in there, then search with 'IN' - but I have to search with LIKE so that I'll be able to use wildcards.

Does anyone have an idea how to solve or work around this issue?

I don't want to split the command into several commands using the same method.

Thanks for your suggestions!

tsql sql-server sql
1 Answers

You can still use a temporary table.

where exists (select 1
              from patterntable pt
              where ' ' + x.field1 + ' ' like pt.pattern
             )

x is the alias for whatever table the field comes from.

Or:

              where ' ' + x.field1 + ' ' like '% ' + pt.pattern + ' %'

depending on whether you want to store the wildcards or not.

user_11440350
Related